Sumproduct giving #Value error

[KeriM]

I'm having some trouble with a sumproduct function. I have a workboo
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 i
regular date format (1/1/2013) in range C5-C35. I have a range in th
"daily" sheet that has numbers in D5-D35. I'd like to do a sumproduct o
that range for those dates in January, but I keep getting a #Valu
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))*(Daily!$D$5:$D$35))

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

 

[Ron Rosenfeld]

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.

 

[Spencer101]

I'm having some trouble with a sumproduct function. I have a workboo
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 i
regular date format (1/1/2013) in range C5-C35. I have a range in th
"daily" sheet that has numbers in D5-D35. I'd like to do a sumproduct o
that range for those dates in January, but I keep getting a #Valu
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))*(Daily!$D$5:$D$35))

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

Hello again KeriM.

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

Also, which version of Excel are you using

 

[KeriM]

Hello again KeriM.

I think if you post an example workbook you'll get a solution ver
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 makin
a sample spreadsheet to upload. I had an empty cell in the range (sinc
Jan 1st was a holiday), so sumproduct wasn't working. I thought the "--
would negate any empty cells, but I guess not

 

[joeu2004]

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

 

[KeriM]

;1608786']On Mon, 21 Jan 2013 14:14:11 +0000, Keri

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 tex
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 publicall
accessible site (e.g. Skydrive) and post a link here so we can se
exactly what's going on.

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