How to make COUNTIF(S) work with arrays?

Rnkhch

Well-known Member
Joined
Apr 28, 2018
Messages
578
Office Version
  1. 365
Platform
  1. Windows
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
ABC
1
2a
31c
4f
51a
61g
7
8
91o
101a
111p
12d
13b
14
15
161q
17h
181a
19a
201g
Sheet1



Thanks for any input!
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
Table filtered on 1 and a

Filter.xlsm
AB
1
23
3ColAColB
71a
121a
201a
23
5a
Cell Formulas
RangeFormula
B2B2=SUBTOTAL(103,B5:B22)
 
Upvote 0
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")))
 
Upvote 0
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
ABCDEF
1
2a100
31c
4f
51xx
61g
7
8
91o
101xx
111p
12d
13b
14
15
161q
17h
181xx
19a
201g
Countif
Cell Formulas
RangeFormula
D2D2=COUNTA(FILTER(B2:B20,ISNUMBER(A2:A20)*(B2:B20="a")))
E2E2=COUNT(FILTER(A2:A20,ISNUMBER(A2:A20)*(B2:B20="a"),""))
F2F2=SUMPRODUCT(--ISNUMBER(A2:A20),--(B2:B20="a"))
 
Last edited:
Upvote 0
Solution
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 ?
 
Last edited by a moderator:
Upvote 0
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.
 
Upvote 0
Thanks all! Very informative. I have also seen slow-down with SUMPRODUCT with large ranges.
 
Upvote 0

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top