COUNTIF in a Range of Dates

  • Thread starter Thread starter David Lipetz
  • Start date Start date
D

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
 
Try

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)
 
Thanks Don, that worked perfectly!

When is it appropriate to use sumproduct? It did not occur to me to even try
it.
 
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)
 
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
 
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)
 
Dave said:
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).
Click to expand...

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
 
A few people have looked at the speed and although it might be neglible with
just a few formulas, the double negative wins.
 
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.
 
Back
Top