Counting data

R

ryadav

Hi

I really hope someone can help me this time, I haven't had much luck in
the other forums.

Basically I am working on a spreadsheet which has 2 columns, one is the
priority column and the other is the Action column. At the end of the
spreadsheet there is a table which totals up the priorities and the
actions. I need the table to reflect the priorites against the action.

The priorites are up to 1 month, up to 6 month and up to 1 year. The
actions are Estates, Student Services and Dean of faculty. So how do i
create a formula which says that the priority of 1 month is allocated
to estates 4 times.

I have tried =COUNTIF($G$4:$H$200,"="&$M5), but that just counts how
many times up to 1 month features in the spreadsheet. I probably need
some sort of IF statement, but i am not sure what?

Can someone please help??

Thanks
 
T

TryfanMan

Hi,

You could try an array formula. This allows you to count, or sum,
based on more than one criteria.

For example:

=sum(if($A$2:$A$200="up to 1 month",if($B$2:$B$200="Estates",1,0)))

Press ctrl & shift when you have entered all the data to tell Excel you
want it to be an array forumla. You will know if it has worked because
it will put braces ( { } ) around the function in the formula bar.

This array function simply performs the function for each cell in the
array and adds one to the total if the conditions are met.

I would also recommend including the cell references of your critera
instead of putting them directly in the function as I have above.

Hope this helps.
 
R

ryadav

Hi

Just to say thankyou very much, I tried what you suggested and it
works.

Thanks again.
 
Top