Sumproduct giving #Value error
 

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!

Sumproduct giving #Value error
 

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.

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?

Sumproduct giving #Value error
 

"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's a real date. I can post later tonight. I don't have access to anything at work.

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.