Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » COUNTIF

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

COUNTIF

Resolved · High Priority · Version 2007

Reply Thu 30 Jun 2011, 12:08Delegate Gavin said...

Gavin has attended:
Excel Intermediate course
Excel Advanced course

COUNTIF

Hi I am trying to make an auto heat map of a risk register so I have 2 columns scoring 1-5 and showing a third RAG muliple of the 2 columns. What I want to do is map the 2 columns 1-5 vs 1-5 scores on a 5x5 grid as counted numbers.
So I am trying to Countif where column Prob is "1" and Impact is "1" and then totalise in the grid where 1 intersects with 1. and likewise for 1-5 vs 1-5 (Ie all 25 grid squares), but I cannot seem t be able to do mulitple Count criteria.
Can you help? Hope that is clear.
Regards

Gavin

For upcoming training course dates see: Pricing & availability

Reply Thu 30 Jun 2011, 13:53Trainer Clare said...

RE: COUNTIF

Hi Gavin,

There is a new function with 2007 called COUNTIFS which allows you to put multiple conditions into your counting. The syntax is just about the same as for COUNTIF but you keep going, so:

=COUNTIFS(range1, condition1, range2, condition2,...)

The logic is that condition1 must be met AND condition2 must be met before anything is counted.

Hope this helps

Clare Glover
Microsoft Applications Trainer

Reply Thu 30 Jun 2011, 14:04Delegate Gavin said...

RE: COUNTIF

Clare

Thanks and that works perfectly on my PC / Excel 2010.
But will not work on Proj Mgr Exel 2003 PCs - is there a 2003 compatible soluion please?
Thanks

Gavin

Reply Thu 30 Jun 2011, 14:29Trainer Clare said...

RE: COUNTIF

Hi Gavin,

Sorry - thought you needed 2007! Your easiest method is probably with a pivot table where prob and impact are your row and column labels. The data in the middle needs to be another column - either a label type of column or indeed prob or impact. Just make sure that you change the field setting to COUNT not SUM if it's a numeric field.

Is this any better?

Clare

Reply Thu 30 Jun 2011, 16:15Delegate Gavin said...

RE: COUNTIF

Perfect thanks so much.

Gav

Excel articles

· Microsoft Excel Training Courses or Consultants: Which Is Better For Your Company?
· Plug Into The Big Grid
· Adding Effects To Drawing Objects In Excel
· Want To Create A Presentation In Excel?
· You're Hired! Raising Your Chances Of Employment With Excel

Please browse our web site to find out more about
basic excel training and other Microsoft training courses.

Excel tip:

Rotating Text in an Excel 2010 Worksheet

Maybe you want to draw attention to certain text or you just simply want to make your worksheet look more exciting!

One of the things you might consider is, rotating the text in a particular cell or set of cells.

1) Select a cell you would like to rotate
2) Click the ''Home'' tab in the Ribbon
3) Click ''orientation'' in the ''Alignment'' section
4) A pop up menu will appear with a few choices, if you want to decide yourself how many degrees to rotate the text, then click ''Format Cell Alignment.''

View all Excel hints and tips