Multi-conditional count

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jason@jasonholm.com - 16 Jun 2007 20:52 GMT

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

Reply to this Message

vsoler - 16 Jun 2007 21:02 GMT

On 16 jun, 21:52, "J...@jasonholm.com" <jason.h...@gmail.com> wrote:

> Hello and thank you for reading.
>
[quoted text clipped - 20 lines]
> B Y Y
> A N Y

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

Reply to this Message

ShaneDevenshire - 16 Jun 2007 22:21 GMT

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 and thank you for reading.
>
[quoted text clipped - 20 lines]
> B Y Y
> A N Y

Reply to this Message

Bernd P - 17 Jun 2007 07:08 GMT

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

Reply to this Message

ShaneDevenshire - 17 Jun 2007 16:30 GMT

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.

Signature

Cheers,
Shane Devenshire

> Hello,
>
[quoted text clipped - 6 lines]
> Regards,
> Bernd

Reply to this Message