Indirect reference within SUMPRODUCT & LEFT

**troop2118** · 06-01-2012, 07:51 AM

Hi,

need urgent help.

currently I am using the following formula which works perfectly fine:

=SUMPRODUCT((LEFT(Jan!$P$9:$P$3000,6)=$F124)*(Jan!$T$9:$T$3000))

However, I would need to make an indirect reference to the Worksheet "Jan" instead of the reference above. The Text "Jan" is in Cell P7 of the same worksheet as the formula above. I can't get the INDIRECT formula right with the SUMPRODUCT and the LEFT condition above.

Anyone able to help me here?

Thanks in advance

**daddylonglegs** · 06-01-2012, 07:54 AM

Try this version with SUMPRODUCT

=SUMPRODUCT((LEFT(INDIRECT(P7&"!P9:P3000"),6)=$F124)*INDIRECT(P7&"!T9:T3000"))

....or it's possibly simpler to use SUMIF with a "wildcard" like this

=SUMIF(INDIRECT(P7&"!P9:P3000"),$F124&"*",INDIRECT(P7&"!T9:T3000"))

**troop2118** · 06-01-2012, 07:58 AM

Thanks, However, I get a #REF! error with both solutions.

**daddylonglegs** · 06-01-2012, 08:01 AM

Both work OK when I tested - is the sheet name just "Jan"? iS that in P7

**troop2118** · 06-01-2012, 08:05 AM

Yes, sheet name is "Jan" & the text "Jan" is in P7.

**daddylonglegs** · 06-01-2012, 08:09 AM

The formula I suggested should work, are you sure P7 is text and not a formatted date? Can you post a sample workbook showing the problem?

**troop2118** · 06-01-2012, 08:12 AM

Ok...somehow it must have to do with P7. I have another INDIRECT formula in one of the other sheets that is also referencing "Jan". I copied that cell (which contains only the text "Jan") into P7 and your proposal works (thanks!). Now I am just curious why.What was wrong with P7's "Jan" before? any ideas? there was no blank or any misspelling there (after all its only 3 letters).

**troop2118** · 06-01-2012, 08:13 AM

Originally Posted by daddylonglegs

The formula I suggested should work, are you sure P7 is text and not a formatted date? Can you post a sample workbook showing the problem?

Maybe that was the reason!!!!

Thanks a lot - it works!!!

**daddylonglegs** · 06-01-2012, 08:33 AM

This version will will either way, if P7 contains the text "Jan" or a January date

=SUMPRODUCT((LEFT(INDIRECT(TEXT(P7,"mmm")&"!P9:P3000"),6)=$F124)*INDIRECT(TEXT(P7,"mmm")&"!T9:T3000"))