Count Cells using 3 criteria

[mls2125]

I saw the sumproduct thread below with 2 criteria but I need 3. Can anyone help me with 3 criteria?

A B C D E F G H
1 YES YES YES YES YES YES Single Mengor
2 YES YES YES YES YES YES Single Hyatt
3 NO YES YES YES YES NO Double Hyatt
4 NO YES YES YES YES NO Double Mengor
5 NO YES YES YES YES NO Single Mengor
6 NO YES YES YES YES NO Single Mengor
7 NO YES YES YES YES NO Single Mengor
8 NO YES YES YES YES NO Single Mengor

So if I wanted to count the "YES" in column A, the "Single" in column G and Hyatt in column H and they must meet all 3 criteria, how would I create the formula?

[BenjieLop]

I saw the sumproduct thread below with 2 criteria but I need 3. Can anyone help me with 3 criteria?

A B C D E F G H
1 YES YES YES YES YES YES Single Mengor
2 YES YES YES YES YES YES Single Hyatt
3 NO YES YES YES YES NO Double Hyatt
4 NO YES YES YES YES NO Double Mengor
5 NO YES YES YES YES NO Single Mengor
6 NO YES YES YES YES NO Single Mengor
7 NO YES YES YES YES NO Single Mengor
8 NO YES YES YES YES NO Single Mengor

So if I wanted to count the "YES" in column A, the "Single" in column G and Hyatt in column H and they must meet all 3 criteria, how would I create the formula?

The formula

=sumproduct(--(A1:A8="Yes"),--(G1:G8="Single),--(H1:H8="Hyatt"))

should help you out.

Regards.

[mls2125]

I used your formula and it gave me a zero. If I take the "hyatt" formula out of the equation I get a number. Any other suggestions?

[BenjieLop]

I used your formula and it gave me a zero. If I take the "hyatt" formula out of the equation I get a number. Any other suggestions?

I do not know what else to say ... I tested the formula and it works.

[BenjieLop]

Maybe, you can try this and see if it works for you.


=sumproduct((A1:A8="Yes")*(G1:G8="Single)*(H1:H8="Hyatt"))


Regards.

[mls2125]

I get a number that is not accurate when using the formula. Can you tell me what the -- means? I notice if I increase or decrease by 1 (-) I get the exact opposite number (negative). I have been trying to use the sumproduct formula for hours now and using the * between () results in #VALUE.

[mls2125]

Is there some IF statement I can use stating that IF H1:H8="Hyatt" and then sumproduct(--(A1:A8="yes"),--(G1:G8="Single")??

The formulas above are not working for me for some reason.

Thanks for anyone's help!