COUNTIF in a Range of Dates

[David Lipetz]

The range Y6:Y399 contains a column of invoice dates formatted as DD/MM/YY.
I need to count how many invoices are issued each month. I thought the
following arrary formula would work to count January invoices, but does not:

{=COUNTIF(Y6:Y399,MONTH(Y6:Y399)=1)}

Ideas?

Thanks,
David

 

[Bob Phillips]

Try

=SUMPRODUCT(--(Y6:Y399<>""),--(MONTH(Y6:Y399)=1))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[Don Guillett]

Use sumproduct

=sumproduct((MONTH(Y6:Y399)=1)*1)

NON array

 

[David Lipetz]

Thanks Don, that worked perfectly!

When is it appropriate to use sumproduct? It did not occur to me to even try
it.

 

[David Lipetz]

Thanks Bob. Your formula also worked, Don's was somewhat less complex.

Plus, I don't understand the use of "--" in your formula (or any formula for
that matter)

 

[Dave Peterson]

Do you have any empty cells in that range?

You may want to try both formulas to see if there's a difference.

The first minus changes true to -1 (and false to 0). The second minus changes
-1 to 1 (and 0 to 0).

=sumproduct() likes to work with numbers, so this is a quick way to change those
boolean values to numbers.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Bob Phillips]

David,

Don's is less complex, but if you have blank cells in the range they get
counted with that formula. My formula is more defensive, it excludes empty
cells.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[George]

Do you have any empty cells in that range?

You may want to try both formulas to see if there's a difference.

The first minus changes true to -1 (and false to 0). The second minus changes
-1 to 1 (and 0 to 0).

Dave,
On a side point regarding the double negative conversion of true & false

Do you know if the double negative has any speed advantage over using *1
or using the N function

=SUMPRODUCT(--(B1:B10<>""),--(MONTH(B1:B10)=1))
=SUMPRODUCT(1*(B1:B10<>""),1*(MONTH(B1:B10)=1))
=SUMPRODUCT(N(B1:B10<>""),N(MONTH(B1:B10)=1))

I'm just wondering if any one of these methods is faster than any other?
Although in todays PC whats a few seconds anyway i suppose?

George

 

[Dave Peterson]

A few people have looked at the speed and although it might be neglible with
just a few formulas, the double negative wins.

 

[George]

Thanks for that info, I'm just one of those curious types
It's what I would of thought, since after all the negate is first up in
the order of execution.