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

	A	B	C
1		PP	Level
2	Person A	Y	S+
3	Person B		P
4	Person C	Y	P-
5	Person D	Y	S
6	Person E		S-
7	Person F		P
8	Person G		P
9	Person H	Y	S+
10	Person I	Y	S-
11	Person J	Y	P
12	Person K	Y	S+
13	Person L		P-

<colgroup><col><col><col span="2"></colgroup><tbody>
</tbody>

 

[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.

	A	B	C	D
1		PP	Level 1	Level 2
2	Person A	Y	S+
3	Person B		P
4	Person C	Y		S
5	Person D	Y	S-
6	Person E		S-
7	Person F	Y		P
8	Person G		P
9	Person H	Y	P
10	Person I	Y	P-
11	Person J	Y		S+
12	Person K	Y		P
13	Person L		P-

<colgroup><col><col><col span="3"></colgroup><tbody>
</tbody>