SumProduct or CountIf

[Guest]

I am trying to implement the following function to count the number of
entries I have for January, February, etc.

=SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January"))

It works fine for February, March, etc. but not for January because it reads
the empty cells as being 01/01/1901. Any suggestions? Thanks.

 

[Roger Govier]

Hi Kim

Try
=SUMPRODUCT(--(TEXT(SheetName!J5:J8,"mmmm")="January")*(SheetName!J5:J8,<>))

 

[Guest]

Thank you. I could not get this to work. Excel tells me my function
contains an error.

 

[JE McGimpsey]

That gives me an error. Perhaps something like this, instead:

=SUMPRODUCT(--(MONTH(SheetName!J5:J8)=1),--(SheetName!J5:J8<>""))


Note that if you're going to do the array multiplication first, using *,
there's no reason to use the double unary minuses (--). If you're going
to let SUMPRODUCT to the array multiplication by using the comma
notation, the double unary minuses are needed. The latter is slightly
more efficient.

 

[JE McGimpsey]

Correction:

With multiple terms, one only need use at least one unary minus per
conditional, as long as the total number of unary minuses is even.

I find it's easier just to use double unary minuses for each conditional
all the time, if only to avoid thinking/explaining.

 

[Guest]

Thank you. I got it to work.
=SUMPRODUCT(--(TEXT(SheetName!K4:K800, "mmmm")="January"),
--(Countrywide!K4:K800<>0))

 

[Dave Peterson]

You may want to add that --(SheetName!K4:K800<>"") portion into your formula.

Try clearing the contents on sheetname!K4.

An empty cell will look like January when you do: =text(a1,"mmmm")

 

[KL]

JFYI: this formula won't work in any other version of the Office, but
English. If your application may be used internationally you're better off
using the formula proposed by JE McGimpsey

Regards,
KL