Excel array countif formula

arrays excel excel-formula countif
Surbhi Manocha picture Surbhi Manocha · Jan 4, 2018 · Viewed 15.2k times · Source

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")

Answer

CallumDA picture CallumDA · Jan 4, 2018

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,""))

Related questions

How to clear the entire array?

I have an array like this: Dim aFirstArray() As Variant How do I clear the entire array? What about a collection?

Read More
Creating an Array from a Range in VBA

I'm having a seemingly basic problem but can't find any resources addressing it. Simply put, I just want to load the contents of a Range of cells (all one column) into an Array. I am able to accomplish this by …

Read More
Excel VBA: Variants in Array Variables

A question on variants. Im aware that variants in Excel vba are both the default data type and also inefficient (from the viewpoint of overuse in large apps). However, I regularly use them for storing data in arrays that have …

Read More