Excel array countif formula

Question

I want to use COUNTIF function to evaluate how many items out of 2,0,0,5 are greater than 2? In Countif function, first argument is range and second is criteria. I have tried the below formula. Even tried using Ctrl+Shift+Enter at the end to evaluate. But doesn't seem to work.

=COUNTIF({"2","0","0","5"},">2")

score 16 · Accepted Answer · edited Feb 13 '18 at 08:17

16

COUNTIF doesn't accept array constants (as far as I know). Try this:

=SUMPRODUCT(--({2,0,0,5}>2))

You could also create a countif-style formula like this (the combination ctrl+shift+enter):

=COUNT(IF({2,0,0,5}>2,1,""))

edited Feb 13 '18 at 08:17

ajaysinghdav10d

1,771
3
23
33

answered Jan 04 '18 at 13:04

CallumDA

12,025
6
30
52

4

Agreed, all of the "IFS" family of functions are the same, the "range" arguments do need to be worksheet ranges, not arrays – barry houdini Jan 04 '18 at 13:21
Thanks this wokred. Any other way I can use Countif only to solve my problem ? – Surbhi Manocha Jan 04 '18 at 13:28
@SurbhiManocha, as barry said, it seems like you can't use `COUNTIF`, but you can use `COUNT` with `IF` -- see my edit for details if that helps – CallumDA Jan 04 '18 at 13:33
@SurbhiManocha Not with an array constant. You could put the values into separate cells (eg = countif(a1:a5,">2"). – Ron Rosenfeld Jan 04 '18 at 15:01

score 3 · Answer 2 · answered Jan 04 '18 at 15:21

3

Recommended reading: Array vs Range

Some functions like Offset, SumIf, CountIf, SumIfs, and CountIfs are designed to operate only on (multi-cell) range objects. Sum, SumProduct, Frequency, Linest, lookup functions, etc. take both range and array objects.

Array means: {2,0,0,5}

Range means:

To use countif, you have to use range in cells, defining the array in the formula on the go will not work.

=COUNTIF(A1:A4,">"&2)

answered Jan 04 '18 at 15:21

Ames

460
2
11

I am using this in VBA with Evaluate formula. In range, I want to pass values instead of range. For Example : EVALUATE(sum{1,2,3})) is working fine but EVALUATE(sumif{1,2,3},">1") is not working. – Surbhi Manocha Jan 05 '18 at 05:57
2

Because {1,2,3} does not work with sumifs. Please read up: https://www.mrexcel.com/forum/excel-questions/407180-i-want-understand-difference-between-range-array.html – Ames Jan 05 '18 at 06:17

BriL · Answer 3 · 2022-04-21T00:50:38.263

I know this thread is a few years old, but I ended up here with a similar problem (how to use arrays, not ranges, with countif).

Although my end goal was a little different (I wanted to find items common to two arrays), I figure the workaround I came up with might be useful for others: I ended up using the "match" function coupled with "isnumber". The formula looked like this:

=isnumber(match({a},{b},0))

this will return an array of true/false which corresponds to the values in {a} that are also in {b}. In case it wasn't clear, {a} and {b} are arrays...

Excel array countif formula

3 Answers3

Linked