COUNTIF

[Mark2Aus]

Hi everyone!

B C
3 4 41
4 3 21
5 7 11
6 5 9
7 4 34
8 14 37

I need to build a summary table and in B20 I need to show the number of times that B3:B8<=5 AND C3:C8<=5; in B21 I need to show the number of times that B3:B8<=5 AND C3:C8 is between 5 and 8; in B22 I need to show the number of times that B3:B8<=5 AND C3:C8 is between 9 and 11; etc.
In C21 I need to show the number of times that B3:B8 is between 5 and 8 AND C3:C8 is <=5; in C22 I need to show the number of times that B3:B8 is betwen 5 and 8 AND C3:C8 is betwen 5 and 8; etc...

Is there a way I can do a COUNTIF subject to 2 different conditions =COUNTIF(AND(B3:B8,"<=5"),(C3:C8,"<=5")?
Thanks for any help

 

[Remou]

Hi
This works for me:

=COUNTIF(B2:B7:C2:C7,"<=5")

 

[GlennUK]

Assuming that you want to count corresponding occurrences, ( eg B3 <= 5 and C3 <=5; plus B4 <=5 and C4 <= 5 ; etc etc ), then you need something like this:

=SUMPRODUCT((B3:B8>5)*(B3:B8<=8)*(C3:C8>9)*(C3:C8<=11))

this formula is for the case of B3:B8 greater than 5 and less than ( or equal to ) 8, AND C3:C8 greater than 9 and less than ( or equal to ) 11. Adjust the formula for all of the cases you need.




Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[BenRowe]

I found the following FAQ for an excellent explanation of how SUMPRODUCT works

http://www.tek-tips.com/faqs.cfm?fid=4725

as COUNTIF for multiple criteria.

 

[KenWright]

IMO you won't find a better page on SUMPRODUCT than this one:-

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------