COUNTIF - multiple conditions

[allphin]

Hello -

I'm a relativley inexperienced excel user, and am having trouble usin
multiple criteria for a countif function. I've looked through two book
and the FAQ here, and am striking out (seems simple, so I know I'
missing something easy).

I'm trying to have a cell display the total number of entries in
column that fit criteria in two other columns.

For example, in the below, I'd like to know how many names in Column
are both Blue (Column B) and Low (Column C)

A B C
1 Roger Blue High
2 Mike Red High
3 Fred Blue Low
4 Casey Red Low
5 John Blue High
6 Jeff Red Low

I think this is a COUNTIF function, but I can't figure out how to mak
the AND work. If anybody could offer any thoughts, I'd be ver
appreciative.

Thanks,
Chri

 

[RagDyer]

One way would be to enter the Column B criteria in say D1, and the Column C
criteria in D2, then try this formula:

=SUMPRODUCT((A1:A100<>"")*(B1:B100=D1)*(C1:C100=D2))

--
HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

 

[allphin]

worked perfectly - much appreciated.

C

 

[Guest]

Hi,

You may try using array formulas (Ctrl+Shift+Enter) as well.

=sum(if((B1:B6="Blue")*(C1:C6="Low"),1,0))

Regards,

 

[Ragdyer]

Thanks for the feed-back.