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
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
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
