SUMPRODUCT: Help with text #VALUE, and INDIRECT(SUMPRODUCT)

**rasonline** · 03-08-2011, 05:36 PM

Hi guys

Need help with 2 problems using sumproduct:
1. COnstantly getting #VALUE error no matter what formula method is used
2. Possible to use INDIRECT formula to create a SUMPRODUCT?

I've attached a workbook.
Using Excel 2007.

Problem 1:
I have the following sumproduct formula which works fine as it stands:

=SUMPRODUCT(--(E$6='2'!$C$2)*--(VALUE($D8)=VALUE(TRIM('2'!$A$3:$A$253))),('2'!$C$3:$C$253))

However, if i extend the row references to beyond row 253, then I get the #VALUE error. There are text entries in row 254 onwards.

I've tried various combinations with the unaries, using commas instead of " * ", etc but simply cannot get it to work no matter what I do.

Ideally I would like the formula to read across these columns and rows:
=SUMPRODUCT(--(E$6='2'!$C$2:$BA$2)*--(VALUE($D9)=VALUE(TRIM('2'!$A$3:$A$500))),('2'!$C$3:$BA$500)))

Any suggestions?

Problem 2:
You will see in the attached workbook (yellow cells) that I need to make the formula references to columns-on-Sheet2 dynamic. I am able to identify the column numbers to which the SUMPRODUCT should refer to.

Using A1 notation, I have no clue how to get this to work.
(Due to workbook structure, I cannot use a VLOOKUP of column labels vs column numbers i.e. cannot create list of A-Z with corresponding column numbers.)

So I then changed to R1C1 notation, AND tried to use INDIRECT formula to make the column references dynamic.

The formula in A1 notation: =SUMPRODUCT(--(E$6='2'!$C$2)*--(VALUE($D8)=VALUE(TRIM('2'!$A$3:$A$253))),('2'!$C$3:$C$253))

The formula in R1C1 notation (excel converted): =SUMPRODUCT(--(R6C='2'!R2C3)*--(VALUE(RC4)=VALUE(TRIM('2'!R3C1:R253C1))),('2'!R3C3:R253C3))

Then using INDIRECT, i substituted the column references with the dynamic references (manually typed in):

=INDIRECT("SUMPRODUCT(--(R6C='2'!R2C"&R2C&")*--(VALUE(RC4)=VALUE(TRIM('2'!R3C"&R1C&":R253C"&R1C&"))),('2'!R3C"&R2C&":R253C"&r2c&"))")

However, this results in #REF.

Am really at my wits end with this one. Any help appreciated, or even alternative suggested approaches to achieving the end result.

Thanks.

**bentleybob** · 03-08-2011, 06:41 PM

Problem #1 is easy. It's because your reference is to sheet "2" and, in cell A254, you have some odd character, not a string that can be converted to a value. Put a number in there and the formula works. Of course, it looks like your data really ends at 253, at least for the columns critical to this formula (A and C).

On to #2 ...

**martindwilson** · 03-08-2011, 07:32 PM

also you dont need * and -- not sure how it affects things
SUMPRODUCT(--(E$6='2'!$C$2),--(VALUE($D8)=VALUE(TRIM('2'!$A$3:$A$253)
but would be the way to use --
and then again
E$6='2'!$C$ that range is not the same size as 2'!$A$3:$A$253
still not sure what you are trying to do
perhaps sumifs should be used

**daddylonglegs** · 03-08-2011, 08:00 PM

For problem 1 you can use this formula

=IF(D9=0,"",SUMPRODUCT((E$6='2'!$C$2:$BA$2)*($D9&""='2'!$A$3:$A$500),'2'!$C$3:$BA$500))

by converting D9 to text you don't need VALUE function so that avoids error caused by using VALUE on A254.

For problem 2 - doesn't problem 1 formula achieve the same thing? Do you just want a different approach or are you trying to achieve something different?

**rasonline** · 03-09-2011, 03:31 AM

Problem 1:

Originally Posted by bentleybob

Of course, it looks like your data really ends at 253, at least for the columns critical to this formula (A and C).

E$6='2'!$C$ that range is not the same size as 2'!$A$3:$A$253

To keep it simple for now, I used only column C. However, I wanted one single formula that would cover columns C - BA. The reference to E6 is a date reference. So the final version will read E$6='2'!$C$2:$BA$2.

I am setting up this spreadsheet for somebody in my dept who will simply copy and paste info into sheet "2" on a daily basis, and I will view the summary.

Problem 1 now solved thanks to "daddylonglegs"

In my all time, I have never thought of converting a number back to text to do match. Brilliant.

Problem 2 described in separate post, and file updated.

**rasonline** · 03-09-2011, 05:58 AM

Thanks to Daddylonglegs for solution to PROBLEM 1.
For Problem 2, I’ve given it some more thought and we can possibly completely ignore problem 1.

Objective:
Assistant user pastes info in 3 columns every day on sheet “2”. Day 1 is column A-C. Day 2 is column D – F and so on. I would like EDG sheet to automatically summarise info according to date, based on the daily entries that are pasted on sheet “2”.

This formula works on sheet EDG for the date 02-Jan
=SUMPRODUCT(--($B8&""='2'!$A$3:$A$1000),('2'!$C$3:$C$1000))

For the date 03-Jan, the formula needs to be
SUMPRODUCT(--($B8&""='2'!$D$3:$D$1000),('2'!$F$3:$F$1000))

For each successive day, the ranges will need to change.
I was thinking of doing this dynamically using an INDIRECT FORMULA.
I’ve inserted 2 rows which identify the relevant columns for each day (shown on rows 1 and 2).
The relevant columns are displayed as numbers and so i thought it is necessary to use the R1C1 notation.

However, having some problems with the indirect formula.
If i take a straightforward formula as below, then the INDIRECT formula works.

=Descrip!B31
=INDIRECT("Descrip!B31")
=INDIRECT("Descrip!"&"B31")

Note in the 2nd case that the result is correct.
However, this formula doesn’t work

=SUMPRODUCT(--($B8&""='2'!$A$3:$A$1000),('2'!$C$3:$C$1000))
=INDIRECT(“SUMPRODUCT(--($B8&""='2'!$A$3:$A$1000),('2'!$C$3:$C$1000))”)
Returns a #REF

Tried a formula like this but also doesn’t work
=INDIRECT("SUMPRODUCT(--($B8&""='2'!$A$3:$A$"&$D$4&"),('2'!$C$3:$C$1000))")
where the value in cell D4 is 1000
but this also returns a #REF

So before even looking at R1C1 notation, it doesn’t look like INDIRECT(“SUMPRODUCT..”) will work?

**rasonline** · 03-09-2011, 08:01 AM

Seeing as this query is now moving away from the original query, I'm closing off her and creating a separate thread for INDIRECT("SUMPRODUCT...").

Thanks for the help so far guys.