Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Member
 
Posts: 70
Default 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!

Last edited by KeriM : January 21st 13 at 02:40 PM
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default 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.
  #3   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by KeriM View Post
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?
  #4   Report Post  
Member
 
Posts: 70
Default

Quote:
Originally Posted by Spencer101 View Post
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?
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 829
Default 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).






  #6   Report Post  
Member
 
Posts: 70
Default

Quote:
Originally Posted by Ron Rosenfeld[_2_] View Post
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.
  #7   Report Post  
Member
 
Posts: 70
Default

Quote:
Originally Posted by joeu2004[_2_] View Post
"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.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct keeps giving zero Fred Excel Discussion (Misc queries) 3 October 16th 09 05:04 PM
Using NAMES in SUMPRODUCT giving error SFC Traver Excel Worksheet Functions 2 June 26th 08 09:51 PM
SUMPRODUCT is giving incorrect number Richard Excel Discussion (Misc queries) 2 June 22nd 07 11:17 AM
SUMPRODUCT - Giving me trouble porter444 Excel Worksheet Functions 3 May 22nd 07 11:04 AM
Sumproduct giving #NA Gary Excel Worksheet Functions 2 August 3rd 06 11:47 AM


All times are GMT +1. The time now is 08:34 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"