How to make COUNTIF(S) work with arrays?

[Rnkhch]

Hello,

Unless I'm missing something, it appears to me that both COUNTIF and COUNTIFS only work with ranges, i.e. continuous selections of cells. When I try to put a filtered array for the range parameter, the formulas don't accept it.

For instance, in the attached XL2BB, I'm trying to count how many cells in column B have the letter "a", but I want to filter the range based on whether column A is number or not. (I want to do this to make the ranges as small as possible because my actual data is so big with a lot of empty rows in between chunks of data.) So I'm trying to write a formula like this:

=COUNTIF(FILTER(B2:B20,ISNUMBER(A2:A20)),"a")

But after hitting ENTER, it gives me error, so I can't even input this formula. Any suggestions on how to make COUNTIF(S) work with arrays? (This same type of filtering works very well with other formulas that accept arrays in their parameters, such as INDEX, etc.)

Book1
	A	B	C
1
2		a
3	1	c
4		f
5	1	a
6	1	g
7
8
9	1	o
10	1	a
11	1	p
12		d
13		b
14
15
16	1	q
17		h
18	1	a
19		a
20	1	g
Sheet1

Thanks for any input!

 

[Dave Patton]

Table filtered on 1 and a

Filter.xlsm
	A	B
1
2		3
3	ColA	ColB
7	1	a
12	1	a
20	1	a
23
5a

Cell Formulas
Range	Formula
B2	B2	=SUBTOTAL(103,B5:B22)

 

[StephenCrump]

You're right, COUNTIF(S) requires a range. So you'll need a workaround.

Another way you could do it: =COUNTA(FILTER(B2:B20,ISNUMBER(A2:A20)*(B2:B20="a")))

 

[Rnkhch]

Awesome, thank you so much guys!

 

[Peter_SSs]

Another way you could do it: =COUNTA(FILTER(B2:B20,ISNUMBER(A2:A20)*(B2:B20="a")))

That will incorrectly return a result of 1 if there are no rows that meet the criteria - see cell D2 below. I would suggest E2 formula instead.
(There is also the old-fashioned, though generally less efficient if the range is large, SUMPRODUCT approach as in F2)

22 07 03.xlsm
	A	B	C	D	E	F
1
2		a		1	0	0
3	1	c
4		f
5	1	xx
6	1	g
7
8
9	1	o
10	1	xx
11	1	p
12		d
13		b
14
15
16	1	q
17		h
18	1	xx
19		a
20	1	g
Countif

Cell Formulas
Range	Formula
D2	D2	=COUNTA(FILTER(B2:B20,ISNUMBER(A2:A20)*(B2:B20="a")))
E2	E2	=COUNT(FILTER(A2:A20,ISNUMBER(A2:A20)*(B2:B20="a"),""))
F2	F2	=SUMPRODUCT(--ISNUMBER(A2:A20),--(B2:B20="a"))

 

[StephenCrump]

That will incorrectly return a result of 1 if there are no rows that meet the criteria ...

 

[Phuoc]

One way:

=COUNTIFS(A2:A20,">"&-10^35,B2:B20,"a")

 

[BSALV]

There is also the old-fashioned, though generally less efficient if the range is large, SUMPRODUCT approach as in F2

Is the Filter-fonction calculation different then a sumproduct-calculation, less powerfull, demands less resources ?

 

[Peter_SSs]

Is the Filter-fonction calculation different then a sumproduct-calculation, less powerfull, demands less resources ?

I haven't actually tested this particular comparison - rather commenting on past experience with sumproduct being a bit slow over large ranges.

 

[Rnkhch]

Thanks all! Very informative. I have also seen slow-down with SUMPRODUCT with large ranges.