Count Based on Multiple Criteria

**jaexcel** · 06-07-2012, 11:53 AM

Column A
1
2
3
4
5
6
7
8
9
10
11

Column B
1
2
3
4
5
6
7

Column C
1
2
3
4
5
6
7

I need to count the occurences of the values 1, 2, 3, 4, 6 in Columns B & C if the criteria in Column A is satisfied which is Column A values must be either 1, 2, 3, 4, 7 or 11.

What is the most efficient way to do this?

Thank you

**NBVC** · 06-07-2012, 12:01 PM

I am a little confused... the 3 columns are not the same size.... and am not sure exactly what you mean? what are the b and c criteria, if any?

**jaexcel** · 06-07-2012, 12:12 PM

Countif-Example.jpg

In the attached example: I want to count all instances in columns B to J where numbers 1,2,4,6,7 occur buy only if column A is equal to 1,2,3,4,7,11

**NBVC** · 06-07-2012, 01:11 PM

So are we saying that in order to count as 1, a specific row in column A must contain one of 1,2,3,4,7,11 and columns B:J in the same row must contain any (or all) of 1,2,4,6,7?

**jaexcel** · 06-07-2012, 01:30 PM

That is correct.

**NBVC** · 06-07-2012, 02:25 PM

Try something like:

=SUMPRODUCT(--ISNUMBER(MATCH(A1:A100,{1,2,3,4,7,11},0)),--(MMULT(--(ISNUMBER(MATCH(B1:J100,{1,2,4,6,7},0))),{1;1;1;1;1;1;1;1;1})>=1))

where row 100 is bottom of range.. adjust as necessary.