Possible to use COUNTIF with a function on the range's values?

0
votes

Let's say that I have a list of dates starting from A1 and going across...

1/3/2014 2/5/2014 5/5/2015 8/10/2016 ...

I'd like to count the number of times a certain month appears in this range. My current solution is that the row below it just contains =MONTH(x1), where x is the column, and then I call a COUNTIF on that row.

I don't think that's so bad of a solution, but it does require a whole bunch of extra cells just to calculate months in my spreadsheet, which isn't really necessary for anything else.

So basically, is there any way to do something along the lines of =COUNTIF(MONTH(range),5) to count, for example, the number of times something occurs in May?

1
excelanonymous-functioncountif

1 Answers

4
votes

No, you can't do that, COUNTIF function requires a range as first argument - any operation on a range (like using MONTH function) converts that range to an array that COUNTIF doesn't accept

Possible alternative are to use SUMPRODUCT e.g.

=SUMPRODUCT((MONTH(range)=5)+0)

or COUNTIFS like this

=COUNTIFS(range,">="&Z1,range,"<"&EOMONTH(Z1,0)+1)

where Z1 is 1st of the month to count, e.g. 1-May-2013

Of course the SUMPRODUCT version doesn't take account of the year (although you could add that in) while COUNTIFS does

Explanation

In SUMPRODUCT when you use an expression like MONTH(range)=5 that returns an "array" of TRUE/FALSE values like {TRUE;FALSE;FALSE;TRUE}....but SUMPRODUCT here only sums numbers so we need a way to "co-erce" TRUE to 1 and FALSE to 0. You can do that with any mathematical operation that doesn't change the value, e.g. +0, *1 or you can add -- to the front like this:

=SUMPRODUCT(--(MONTH(range)=5))

so we get something like

=SUMPRODUCT(--({TRUE;FALSE;FALSE;TRUE}))

...and that becomes

=SUMPRODUCT({1;0;0;1})

and then SUMPRODUCT sums those values to get 2, i.e. the number of dates in May.

SUMPRODUCT is preferred to SUM purely because you don't need to "array enter" the formula with CTRL+SHIFT+ENTER

See here for a good explanation of SUMPRODUCT and it's many uses