Multi-conditional count

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

Hello and thank you for reading.

I'm trying to get a multi-conditional count of items from my table of
data as follows:

Say a teacher has a class of students that are going to go on a field
trip to the theme park and to do so the student has to have an "A"
they had to of paid and they have a note from their parent. This
unfortunately has to be done with text (I know).

I want to count the number of students who get to go on this field
trip. This should come to three students. I tried to use "countif" but
to no avail. Any suggestions or ideas are sincerely appreciated.

Grade Paid Note
A N Y
A Y N
A Y Y
A Y Y
B Y N
A N N
A Y Y
B Y Y
A N Y
 
Hello and thank you for reading.

I'm trying to get a multi-conditional count of items from my table of
data as follows:

Say a teacher has a class of students that are going to go on a field
trip to the theme park and to do so the student has to have an "A"
they had to of paid and they have a note from their parent. This
unfortunately has to be done with text (I know).

I want to count the number of students who get to go on this field
trip. This should come to three students. I tried to use "countif" but
to no avail. Any suggestions or ideas are sincerely appreciated.

Grade Paid Note
A N Y
A Y N
A Y Y
A Y Y
B Y N
A N N
A Y Y
B Y Y
A N Y
Click to expand...

Use SUMPRODUCT((A1:A100="A")*(B1:B100="Y")*(C1:C100="Y"))
You will get the number you want
 
Hi,

Here are two variations on the standard theme:

=SUMPRODUCT(N(A1:A9&B1:B9&C1:C9="AYY"))
=SUM(N(A1:A9&B1:B9&C1:C9="AYY"))
The second formula is array entered - press Shift Ctrl Enter when you enter
it.

Cheers,
Shane Devenshire
 
Hello,

Unfortunately both formulas are wrong because they would accept "AY"
"" "Y" in cells A1, B1, C1, for example.

I would prefer
=SUMPRODUCT(--(A1:A100="A"),--(B1:B100="Y"),--(C1:C100="Y"))

Regards,
Bernd
 
The answer is based strictly on users description and sample data will work
fine. Nothing wrong with the other solutions either, these are just
variations on common themes.
For example a grade of AY seems unlikely from my experience in education,
although an incorrect entry could cause that, but then the incorrect entry of
Q would also cause a problem with respect to the final answer. From the
description it sounds like Y and N mean Yes and No and are exclusive and
inclusive, hense and entry of YN or AY or any other in the second and third
columns will give an incorrect result but that is because it is an incorrect
entry.

But if the data were anything else where the answers were not apparently
preset, the more standard SUM or SUMPRODUCT versions should be used.
 
Back
Top