COUNTIF - multiple conditions
 

Hello -

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

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

For example, in the below, I'd like to know how many names in Column A
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 make
the AND work. If anybody could offer any thoughts, I'd be very
appreciative.

Thanks,
Chris


--
allphin
------------------------------------------------------------------------
allphin's Profile: http://www.excelforum.com/member.php...o&userid=27337
View this thread: http://www.excelforum.com/showthread...hreadid=468379

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" wrote in
message ...

Hello -

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

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

For example, in the below, I'd like to know how many names in Column A
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 make
the AND work. If anybody could offer any thoughts, I'd be very
appreciative.

Thanks,
Chris


--
allphin
------------------------------------------------------------------------
allphin's Profile:
http://www.excelforum.com/member.php...o&userid=27337
View this thread: http://www.excelforum.com/showthread...hreadid=468379

worked perfectly - much appreciated.

CA


--
allphin
------------------------------------------------------------------------
allphin's Profile: http://www.excelforum.com/member.php...o&userid=27337
View this thread: http://www.excelforum.com/showthread...hreadid=468379

Hi,

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

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

Regards,

"allphin" wrote:


Hello -

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

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

For example, in the below, I'd like to know how many names in Column A
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 make
the AND work. If anybody could offer any thoughts, I'd be very
appreciative.

Thanks,
Chris


--
allphin
------------------------------------------------------------------------
allphin's Profile: http://www.excelforum.com/member.php...o&userid=27337
View this thread: http://www.excelforum.com/showthread...hreadid=468379

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"allphin" wrote in
message ...

worked perfectly - much appreciated.

CA


--
allphin
------------------------------------------------------------------------
allphin's Profile:
http://www.excelforum.com/member.php...o&userid=27337
View this thread: http://www.excelforum.com/showthread...hreadid=468379