Sumproduct yields #Value! Error

Bonz · Jan 10, 2009

This is being posted as a new thread per instructions suggested here:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
http://www.mrexcel.com/forum/showthread.php?t=362008<o

></o

>
<o

></o

>
I’ve been attempting to resolve a Sumproduct issue with the following formula:
SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=E$12),--(WorkOrderType="PM"),--(Labor))

Using Aladin’s instructions above
Range Name “Labor” in column G containing numeric values is defined as:

=OFFSET('Raw Data'!$G$2,0,0,SizeG)

where “SizeG” is:

=MATCH(9.99999999999999E+307,'Raw Data'!$G:$G)-MIN(ROW('Raw Data'!$G$2))+1

has in my case generated a #Value! Error.

The problem I suspect is that the named range “Labor” contains blanks both at the start and end of the column.
Attempting to resolve the issue I followed Aladin’s explanation as were detailed here

http://www.mrexcel.com/forum/showthr...t=33856&page=2

which has led me to create the following formula:

SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=G$12),--(WorkOrderType="PM"),--(INDEX(Data,0,7)))

The instructions were clear and I think understood. I tested the following formula:

=OFFSET('Raw Data'!$A$2,0,0,Admin!$B$3,Admin!$B$4)

and saw the needed “march of ants” around my data table(imported via MS Query).
As mentioned the named range “Labor” contains numeric values and is in column 7 of my data table which consists of 8 columns in total A:H.
Unfortunately (for me), I still receive a #Value! Error. I’ve searched the board for additional insight and have found other suggestive solutions but none seem to directly apply. I’m not sure how to continue at this point. Any help would be happily welcomed.

Bonz · Jan 10, 2009

Aladin Akyurek said:
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=G$12),--(WorkOrderType="PM"),--(INDEX(Data,0,7)))<o></o>
<o></o>
with the real ranges substituted for Start_Date, WorkOrderType and Data?

<o

></o

>
<o

></o

>
Start_Date has range C2:C1765 containing date values such as: “1/4/2009 12:00:00 AM” (I am only interested in the date portion which I compare to the mmm-yy formatted TEXT in cell G$12 as indicated in the first array of the Sumproduct function).
WorkOrderType has range B2:B1765 containing text values such as “PM”.
<o

></o

>
“Data”, my table of MS Query imported values, has the range A2:H1765.
Substituting the real ranges into the above formula yields:
<o

></o

>
SUMPRODUCT(--(TEXT(C2:C1765,"mmm-yy")=G$12),--(B2:B1765="PM"),--(INDEX(A2:H1765,0,7)))
<o

></o

>
As always any help is very much appreciated.

Aladin Akyurek · Jan 10, 2009

I guess you missed the question:

Would you re-post

SUMPRODUCT(--(TEXT(Start_Date,"mmm-yy")=G$12),--(WorkOrderType="PM"),--(INDEX(Data,0,7)))

with the real ranges substituted for Start_Date, WorkOrderType and Data?

I posted in your initial thread:

http://www.mrexcel.com/forum/showthread.php?t=362008&page=2

Bonz · Jan 10, 2009

Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

> </o

>
I caught the question just wanted to separate things to make following the thread a bit easier in case anyone else tries following along.

Aladin Akyurek · Jan 10, 2009

Bonz said:
<o></o>
<o></o>
Start_Date has range C2:C1765 containing date values such as: “1/4/2009 12:00:00 AM” (I am only interested in the date portion which I compare to the mmm-yy formatted TEXT in cell G$12 as indicated in the first array of the Sumproduct function).
WorkOrderType has range B2:B1765 containing text values such as “PM”.
<o></o>
“Data”, my table of MS Query imported values, has the range A2:H1765.
Substituting the real ranges into the above formula yields:
<o></o>
SUMPRODUCT(--(TEXT(C2:C1765,"mmm-yy")=G$12),--(B2:B1765="PM"),--(INDEX(A2:H1765,0,7)))
<o></o>
As always any help is very much appreciated.

Assuming that G12 houses a first day date of a month/year of interest like in:

1-Jan-07

Then invoke:

Code:

=SUMPRODUCT(
    --(INT(C2:C1765-DAY(C2:C1765)+1)=G$12),
    --(B2:B1765="PM"),
    INDEX(A2:H1765,0,7))

If satisfied, replace ranges with their names.

Bonz · Jan 10, 2009

Hi Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
Thank you for the valuable assistance.<o

></o

>
G12 contains the following:<o

></o

>
<o

></o

>
=TEXT(EDATE($B$8,-10), "mmm-yy") Formatted as general<o

></o

>
<o

></o

>
And $B$8 contains: =Today()<o

></o

>
<o

></o

>
Row 12 contains months from E12:Q12 so E12 is Jan-08 and Q12 is Jan-09<o

></o

>
<o

></o

>
I suspect this might have a significant effect on your recommendation.

Aladin Akyurek · Jan 10, 2009

Bonz said:
Hi Aladin,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Thank you for the valuable assistance.<o></o>
G12 contains the following:<o></o>
<o></o>
=TEXT(EDATE($B$8,-10), "mmm-yy") Formatted as general<o></o>
<o></o>
And $B$8 contains: =Today()<o></o>
<o></o>
Row 12 contains months from E12:Q12 so E12 is Jan-08 and Q12 is Jan-09<o></o>
<o></o>
I suspect this might have a significant effect on your recommendation.

Change the formual in G2 to:

=EDATE($B$8-DAY($B$8)+1,-10)

You can custom format G2 to display its contents as mmm-yy.

Bonz · Jan 10, 2009

Thank you for ALL of the generous help!

OK, implementing the changes I now have the following:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12)--(WorkOrderType="PM")--(INDEX(Data,0,7)))<o

></o

>
<o

></o

>
With G$12 as:<o

></o

>
<o

></o

>
EDATE($B$8-DAY($B$8)+1,-10) formatted custom mmm-yy<o

></o

>
<o

></o

>
generating a #NA! error instead of the #Value! Error—progress????<o

></o

>
<o

></o

>
I hard coded 0 into all of the blank cells in column 7 (“Labor”) of my data table to see if the blanks were still an issue but the #NA! persists. Am I approaching this the wrong way or missing something?<o

></o

>

Bonz · Jan 10, 2009

I accepted Excel’s suggestion for changing the formula after entering it and did not catch the removal of the “,”.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com

ffice

ffice" /><o

></o

>
<o

></o

>
The correct formula:<o

></o

>
<o

></o

>
SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12),--(WorkOrderType="PM"),INDEX(Data,0,7))<o

></o

>
<o

></o

>
Is returning #Value!<o

></o

>

Aladin Akyurek · Jan 10, 2009

Bonz said:
I accepted Excel’s suggestion for changing the formula after entering it and did not catch the removal of the “,”.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
The correct formula:<o></o>
<o></o>
SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12),--(WorkOrderType="PM"),INDEX(Data,0,7))<o></o>
<o></o>
Is returning #Value!<o></o>

What result do you get with?...

=SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12))

=SUMPRODUCT(--(WorkOrderType="PM"))

=SUMPRODUCT(INDEX(Data,0,7))

Sumproduct yields #Value! Error

Bonz

Board Regular

Excel Facts

Bonz

Board Regular

Aladin Akyurek

MrExcel MVP

Bonz

Board Regular

Aladin Akyurek

MrExcel MVP

Bonz

Board Regular

Aladin Akyurek

MrExcel MVP

Bonz

Board Regular

Bonz

Board Regular

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

Sumproduct yields #Value! Error

Board Regular

Excel Facts

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

MrExcel MVP

Board Regular

Board Regular

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock