Countif with two conditions

[Jean-Bernard STROOBANT]

Hello,

I want to make a formula to count how many occurences of a double condition
I have:
IF column A = Cell 1 AND Column B = "Yes" Count.

I have tried many of proposals on several sites, but it didn't work.

Did someone has an idea please ?

Many Thanks.

 

[Jarek Kujawa]

have you tried:

=SUMPRODUCT((A1:A100=Cell1)*(B1:B100="Yes"))

?

 

[Max]

An example, in C1:
=SUMPRODUCT((A2:A100=A1)*(B2:B100="Yes"))

will count cells within A2:B100 which satisfy the dual criteria of:

A2:A100=A1
AND
B2:B100="Yes"
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik

 

[Jean-Bernard STROOBANT]

Thanks a lot Guys, it works.

 

[Vicki]

Max,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is employees under 54
or equal and yrs of svc under 9yrs or less.

Is countif the right formula?
Please help.

 

[T. Valko]

I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).

This too did not work.

Try removing the quotes from around the numeric values:

=SUMPRODUCT(--(Sheet1!F6:F52<=54),--(Sheet1!G6:G52<=9))

Note that if there are any empty cells in either range they may be counted.
An empty cell evaluates as 0 and 0 is <= either of your criteria.

 

[Vicki]

Thank you so much. That was the problem. It now works. YEAH!

 

[T. Valko]

You're welcome!

Just curious, if the formula now works then why did you post the same
question in a new thread?

 

[Greg in CO]

Hi Biff!

I have a similar problem (posted under "To Count or not to Count) and have
received several responses, but none have hit the nail on the head. I want
to compare the existance of entries in two different columns, and then get a
count for the number of entries in the second column:

Column A - Job Position
Column S - hours entry for the month

I want to compare the existance of an entry in Column A and the existance of
an entry in Column S, and if there is an entry in boths columns, count the
entry in Column S. No summing needed. This answers the question: How many
job positions in Column A have entries in Column S?

The folks responding to the other posting have been really great, but the
formulas have not quite hit the mark.

Thanks!

 

[Greg in CO]

Whoo-hoo! After looking at the formula Peo sent me...and having read copius
posts about the SUMPRODUCT formula variations...I had a "Hmmmm" moment and
wondered what I would get if I put the "--" in front of the first array...and
it worked!

Yay! (Happy dance, happy dance).

My thanks! to all the folks who took the time to help...you guys are
great!!!!!

Here is the final formula: =SUMPRODUCT(--(A12:A34<>""),--(S12:S34<>"")),
then press CSE to commit.

Yipee!

 

[T. Valko]

You seem to have gotten it to work.

From the original post:

Here is the final formula: =SUMPRODUCT(--(A12:A34<>""),--(S12:S34<>"")),
then press CSE to commit.

You don't need CSE to commit. Just a normal ENTER will do.