sumproduct with reference to sheet not returning value

**lallo.jr** · 05-15-2010, 01:57 PM

Hello,

A sumproduct formula with text and date criteria is not returning a value and I'm hoping someone else's fresh eyes might be able to help me find out why.

On the attached file, on sheet "budget by month 2010" in cell D11, the formula

=SUMPRODUCT(--(ISNUMBER(SEARCH("lease payment",'register - M&I'!$D$6:$D$42))),--(TEXT('register - M&I'!$C$6:$C$42,"mmmm")="Jan"),'register - M&I'!$F$6:$F$42)

should return the value $452 from sheet "register - M&I" from row 7.

I'm just now starting to review this worksheet; any corrections would be greatly appreciated!

**DonkeyOte** · 05-15-2010, 02:03 PM

In terms of your TEXT command you will find that "mmmm" would return "January" whereas "mmm" would return "Jan"
(ie remove an "m" from your format)

**lallo.jr** · 05-15-2010, 02:07 PM

Yes, i see that now. Will a "mmm" TEXT command always return a 3 letter month abbreviation, including the month of May?

**shg** · 05-15-2010, 02:08 PM

That's a bug. For May it returns the entire month name instead of the 3-letter abbreviation ...

**lallo.jr** · 05-15-2010, 02:15 PM

Haha, ok maybe I'm asking too much from May. My question should have been, will both "mmm" and "mmmm" work for May? I'm sure this follow up question doesn't make me look any smarter that the previous one but I guess that's why I'm here.

Thanks