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

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" <allphin.1vh56c_1126911930.5498@excelforum-nospam.com> wrote in
message news:allphin.1vh56c_1126911930.5498@excelforum-nospam.com...
>
> 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

Thanks for the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"allphin" <allphin.1vhgae_1126926387.7148@excelforum-nospam.com> wrote in
message news:allphin.1vhgae_1126926387.7148@excelforum-nospam.com...
>
> 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
>
>