I'm having some trouble with a sumproduct function. I have a workbook with two sheets. One sheet (monthly) that has a date written in "mmmm" format in B4. I have another sheet (daily) that has the dates written in regular date format (1/1/2013) in range C5-C35. I have a range in the "daily" sheet that has numbers in D5-D35. I'd like to do a sumproduct on that range for those dates in January, but I keep getting a #Value Error. I've tried it many ways:

=SUMPRODUCT(--(Month(B4)=MONTH(Daily!$C$5:$C$35)),(Daily!$D$5:$D $35))

=SUMPRODUCT((Month(B4)=MONTH(Daily!$C$5:$C$35))*(D aily!$D$5:$D$35))

I've even tried putting a 1 in column A on the monthly sheet and referencing that. Still didn't help. Any assistance is greatly appreciated!

Hello again KeriM.

I think if you post an example workbook you'll get a solution very quickly on this.

Also, which version of Excel are you using?

Thanks! I'm using Excel 2010. I actually got it working as I was making a sample spreadsheet to upload. I had an empty cell in the range (since Jan 1st was a holiday), so sumproduct wasn't working. I thought the "--" would negate any empty cells, but I guess not?

"KeriM" wrote:
I had an empty cell in the range (since Jan 1st was a holiday),
so sumproduct wasn't working. I thought the "--" would negate
any empty cells, but I guess not?

No, it does. A truly empty cell is treated as zero.

My guess: what you are calling "empty" is actually a formula that returns
the null string (""). The null string is text; --text results in a #VALUE
error.

One other explanation that is unlikely, but possible: what appears to be
empty actually contains a __constant__ null string.

For example, enter ="" into a cell, copy it, then paste-special-value into
the same or different cell.

The cell will look empty; but ISBLANK(A1) returns FALSE. Of course,
ISBLANK(A1) returns TRUE for a truly empty cell (no formula and no constant
value).

It is a formula that returns a null string. Is there any workaround for that? I have a sumproduct formula in my weekly sheet that works just fine with that range. That's why I'm confused on why it doesn't work on my monthly sheet.

On Mon, 21 Jan 2013 14:14:11 +0000, KeriM wrote:

One sheet (monthly) that has a date written in "mmmm"
format in B4.

What do you mean by this?
Is this a "real date" formatted to display as "mmmm", or is it a text string?

If the latter, then change month(a4) to MONTH(DATEVALUE(A4&" 1, 2012")).

If the former, then post a copy of your workbook to some publically accessible site (e.g. Skydrive) and post a link here so we can see exactly what's going on.

It's a real date. I can post later tonight. I don't have access to anything at work.