SUMPRODUCT

[Miss Hollywood]

I have been working on the following and need a little help.

I have col A and Co B. I would like to have a formula that tells me how many rows the combo 1&5 or 2&5 occurs. I got the 1&5 to work earlier (but its on my work computer) but I could not get the "OR" part to work.


I believe I had a sumproduct formula with --

Answer for below would be 4
A B
1 5
2 5
3 7
4 8
5 9
1 5
2 5
3 7
4 8
5 9

If someone could help that would be most wonderful!

 

[SydneyGeek]

Hi Miss Hollywood, try

=SUMPRODUCT(((A2:A11=1)+(A2:A11=2))*(B2:B11=5))

Denis

 

[Brian from Maui]

Or,

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A10,{1,2},0))),--(B1:B10=5))

Book1
	A	B	C	D
1	1	5	1	4
2	2	5	2	4
3	3	7
4	4	8
5	5	9
6	1	5
7	2	5
8	3	7
9	4	8
10	5	9
Sheet1