Sumproduct yields #Value! Error

[Bonz]

This is being posted as a new thread per instructions suggested here:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><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]

What result do you get with?...

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

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

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

All three generate numeric values as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12)) yields 145

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

=SUMPRODUCT(INDEX(Data,0,7)) yields 49085<o></o>

 

[Aladin Akyurek]

All three generate numeric values as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=SUMPRODUCT(--(INT(Start_Date-DAY(Start_Date)+1)=G$12)) yields 145

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

=SUMPRODUCT(INDEX(Data,0,7)) yields 49085<o></o>

What result do you get with the following?...

=ROWS(Start_Date)

=ROWS(WorkOrderType)

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

 

[Bonz]

What result do you get with the following?...

=ROWS(Start_Date)

=ROWS(WorkOrderType)

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

Also numeric values as follows:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
=ROWS(Start_Date) yields 1765

=ROWS(WorkOrderType) yields 1765

=ROWS(INDEX(Data,0,7)) yields 1655<o></o>
<o></o>
I see the problem “1655” since they all need to be the same range.
<o></o>
I checked the Dynamic named range for data:<o></o>
<o></o>
OFFSET('Raw Data'!$A$2,0,0,Admin!$B$3,Admin!$B$4)<o></o>
<o></o>
Cuts off at row 1656 so that is probably the issue. <o></o>

 

[Bonz]

From Admin sheet (just in case you were going to ask for them)<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
# of Rows =MATCH(BigNum,'Raw Data'!G:G) yields 1656<o></o>
<o></o>
# of data recs =B2-(CELL("Row",'Raw Data'!G2)-1) yields 1655<o></o>
<o></o>
# of data columns is 8<o></o>

 

[Aladin Akyurek]

Would you please not apply any formats to your replies? Your replies all buries within html tags become hard to read...

Didn't we already defined Size, which you could use

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

 

[Bonz]

Would you please not apply any formats to your replies? Your replies all buries within html tags become hard to read...

Didn't we already defined Size, which you could use

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

I was not aware of the formatting. I changed a setting in my User CP which hopefully corrects the issue. All of the board’s posts look the same to me and seem clear--sorry.

I checked our threads and don’t see where we used

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

for the data table range. Your kind suggestion here:

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

was used to help with a named range issue but for a single column.

 

[Aladin Akyurek]

I was not aware of the formatting. I changed a setting in my User CP which hopefully corrects the issue. All of the board’s posts look the same to me and seem clear--sorry.

I checked our threads and don’t see where we used

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

for the data table range. Your kind suggestion here:

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

was used to help with a named range issue but for a single column.

If that definition (of Size) was meant for one of the ranges in the SumProduct formula we have been looking at, you can use it for all other ranges that appear in the formula.

 

[Bonz]

Aladin,

I realized the value for that definition of "Size" after my last post. I have just finished adjusting all of my Dynamic Ranges to remove the COUNTA functions as you have suggested. The Sumproduct formula now works elegantly, returning the needed values.


Your help and time are deeply appreciated. THANK YOU!
Your work is: "ad unguem factus"
ray: