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!

 

[Peter_SSs]

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

I have now done some testing with a large range (40,000 rows) & in fact SUMPRODUCT was about 10% faster than the FILTER suggestion.
However, the suggestion by @Phuoc in post #7 was about 85% faster than the FILTER option. So provided it does what is required - and i think it should - I would go with that for a large range.
For anything not huge though, I would use the one that you understand the best in case you have to modify it later.

 

[Rnkhch]

I see, that's great information; thanks for all the checking. Thanks all again for the various solutions.