Multiple COUNTIFS in same column and in one other

[hmiller]

I'm going to try to word this as well as I can! I have currently got the table below. I want to do a COUNTIFS formula (or at least I think I do) to tell me If column B = Y and column C = S+, S, S- or P.

It's the OR for the second bit I'm struggling with and it's driving me crazy! I can do COUNTIFS column B = Y and Column C = S+ no problem. It's the fact that I want to look up multiple values in column C.

For this example the answer should be 6 but I cannot for the life of me find a formula to get it to work. I'd do it manually by filtering and then counting them but my main spreadsheet has about 1000 people on it!

I hope what I've said makes sense and that someone can help!

Thanks

[TABLE="width: 260"]
<colgroup><col><col><col span="2"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]PP[/TD]
[TD]Level[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Person A[/TD]
[TD]Y[/TD]
[TD]S+[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Person B[/TD]
[TD] [/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Person C[/TD]
[TD]Y[/TD]
[TD]P-[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Person D[/TD]
[TD]Y[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Person E[/TD]
[TD] [/TD]
[TD]S-[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Person F[/TD]
[TD] [/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Person G[/TD]
[TD] [/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Person H[/TD]
[TD]Y[/TD]
[TD]S+[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Person I[/TD]
[TD]Y[/TD]
[TD]S-[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Person J[/TD]
[TD]Y[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Person K[/TD]
[TD]Y[/TD]
[TD]S+[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Person L[/TD]
[TD] [/TD]
[TD]P-[/TD]
[/TR]
</tbody>[/TABLE]

 

[Special-K99]

=sumproduct((b1:b13="y")*((c1:c13="s")+(c1:c13="s+")+(c1:c13="s-")+(c1:c13="p")))

 

[hmiller]

=sumproduct((b1:b13="y")*((c1:c13="s")+(c1:c13="s+")+(c1:c13="s-")+(c1:c13="p")))

THANK YOU, THANK YOU, THANK YOU!

I can now rest my head after banging it against a brick wall for the last few hours!!!

 

[Special-K99]

You're welcome.

* is equivalent to AND

+ is equivalent to OR when using SUMPRODUCT

You may be able to shorten that to

=SUMPRODUCT((B1:B13="y")*((C1:C13={"s","s-","s+","p"})))

{} is an inline array and supplies the four values one after the other to the SUMPRODUCT formula

 

[hmiller]

That has worked brilliantly. Variation on a theme now, I need to do the same thing but with 2 or columns (if that makes sense). So I need to know the sum, if column B (PP) = Y, and column C (Level 1) OR column D (Level 2) = S+, S, S- or P

I tried doing =SUMPRODUCT((B1:B13="y")*((C1:C13={"s","s-","s+","p"}))+((D1:D13={"s","s-","s+","p"})))

as you said the + counts as an OR, but I don't get the right figure out. In the example below the number I need to get is 7. My brain is mushed.


[TABLE="width: 336"]
<colgroup><col><col><col span="3"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]PP[/TD]
[TD]Level 1[/TD]
[TD]Level 2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Person A[/TD]
[TD]Y[/TD]
[TD]S+[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Person B[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Person C[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Person D[/TD]
[TD]Y[/TD]
[TD]S-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Person E[/TD]
[TD] [/TD]
[TD]S-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Person F[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Person G[/TD]
[TD] [/TD]
[TD]P[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Person H[/TD]
[TD]Y[/TD]
[TD]P[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Person I[/TD]
[TD]Y[/TD]
[TD]P-[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Person J[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]S+[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Person K[/TD]
[TD]Y[/TD]
[TD] [/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Person L[/TD]
[TD] [/TD]
[TD]P-[/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]