Prev: Rows to table
Next: 1-4-1 is not a date how do I stop excel from changing whats enter
From: Question?? on 30 Mar 2010 09:51
I am looking for something similar. I have 2 columns that I need to count

A B
8 Yes
2 Yes
6 No
4 Yes

I need to count the number of Yes's in column B for each number, so lets say
I need the number of Yes's for 8. How can I do that? Is it even possible? I
tried using countif but I can only get it to use just the one column and I
need it to filter both.

Thanks for your help!

From: John on 30 Mar 2010 10:10
Hi
Try this =SUMPRODUCT((A1:A9=8)*(B1:B9="yes"))
HTH
John
"Question??" <Question??@discussions.microsoft.com> wrote in message
news:631F9DEB-E59F-4DF0-9146-A442F0A4E584(a)microsoft.com...
>I am looking for something similar. I have 2 columns that I need to count
>
> A B
> 8 Yes
> 2 Yes
> 6 No
> 4 Yes
>
> I need to count the number of Yes's in column B for each number, so lets say
> I need the number of Yes's for 8. How can I do that? Is it even possible? I
> tried using countif but I can only get it to use just the one column and I
> need it to filter both.
>
> Thanks for your help!
>

From: Bernard Liengme on 30 Mar 2010 10:09
In any version of Excel:
=SUMPRODUCT(--(A1:A100=8),--(B1:B100="Yes"))
adjust ranges as needed
In Excel 2007+
=COUNTIFS(A1:A100,8,B1:B100,"yes")
or
=COUNTIFS(A:A,8,B:B,"yes")
best wishes
--
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"Question??" <Question??@discussions.microsoft.com> wrote in message
news:631F9DEB-E59F-4DF0-9146-A442F0A4E584(a)microsoft.com...
> I am looking for something similar. I have 2 columns that I need to count
>
> A B
> 8 Yes
> 2 Yes
> 6 No
> 4 Yes
>
> I need to count the number of Yes's in column B for each number, so lets
> say
> I need the number of Yes's for 8. How can I do that? Is it even possible?
> I
> tried using countif but I can only get it to use just the one column and I
> need it to filter both.
>
> Thanks for your help!
>
 | 
Pages: 1
Prev: Rows to table
Next: 1-4-1 is not a date how do I stop excel from changing whats enter