SumProduct or CountIf
 

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.

Hi Kim

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

--
Regards
Roger Govier
"Kim" wrote in message
...
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.

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

"Roger Govier" wrote:


Hi Kim

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

--
Regards
Roger Govier
"Kim" wrote in message
...
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.

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.

In article ,
"Roger Govier" wrote:

Hi Kim

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

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.


In article ,
JE McGimpsey wrote:

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.

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


"JE McGimpsey" wrote:

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.

In article ,
"Roger Govier" wrote:

Hi Kim

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

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

Kim wrote:

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

"JE McGimpsey" wrote:

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.

In article ,
"Roger Govier" wrote:

Hi Kim

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


--

Dave Peterson

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

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


"JE McGimpsey" wrote:

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.

In article ,
"Roger Govier" wrote:

Hi Kim

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