Solved

Excel SUMPRODUCT query

Posted on 2013-06-18
221 Views
I have written a nice working SUMPRODUCT formula - =SUMPRODUCT(--([DLP1.xlsx]InvRegister!\$B\$4:\$B\$99="Architect"),--([DLP1.xlsx]InvRegister!\$J\$4:\$J\$99=G\$2),--([DLP1.xlsx]InvRegister!\$K\$4:\$K\$99=G\$3),--([DLP1.xlsx]InvRegister!\$L\$4:\$L\$99=\$A6),([DLP1.xlsx]InvRegister!\$N\$4:\$N\$99))

I'd like to replace "Architect" in first part of formula with a cell reference (A1) so I can change the role in A1 and have formula change accordingly.

When I put A1 in place of "Architect" then I get no values returned by formula...

Any ideas? Is it possible or wil I have to just create multiple tabs?
0
Question by:CharAp
• 7
• 3
• 2
• +1

LVL 85

Expert Comment

It should simply be:

=SUMPRODUCT(--([DLP1.xlsx]InvRegister!\$B\$4:\$B\$99=\$A\$1),--([DLP1.xlsx]InvRegister!\$J\$4:\$J\$99=G\$2),--([DLP1.xlsx]InvRegister!\$K\$4:\$K\$99=G\$3),--([DLP1.xlsx]InvRegister!\$L\$4:\$L\$99=\$A6),([DLP1.xlsx]InvRegister!\$N\$4:\$N\$99))
0

Accepted Solution

CharAp earned 0 total points
Brainwave, solved it myself replaced Architect in A1 with ="Architect" and it works, is there a way to just have Architect in A1 or is my workaround the solution?
0

LVL 24

Expert Comment

Are you using Excel 2003 or 2007+?
0

Author Comment

2007
0

LVL 85

Expert Comment

Simply typing Architect in A1 should work just fine. If it doesn't we'd need to see a sample workbook.
0

Author Comment

It doesn't but ="Architect" does work
0

LVL 85

Expert Comment

We'd need to see a sample then because the two should be the same.
0

Author Comment

Not critical as I've linked cell to tab name now using =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) and that speeds matters up even further.

Thanks anyway. I think I posted the query a couple of minutes too soon!
0

LVL 50

Expert Comment

When using CELL function for filename it's better to use a cell reference (any cell reference) otherwise your formula will return the sheet name of the last worksheet where you made a change (not necessarily the one on which the formula resides). E.g. change to

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

regards, barry
0

Author Comment

Actually I changed it to =RIGHT(CELL("filename",\$A\$1),LEN(CELL("filename",\$A\$1))-FIND("]",CELL("filename",\$A\$1),1))

for just that reason
0

LVL 24

Expert Comment

Using 2007, you may find that SUMIFS is a better substitute to SUMPRODUCT.

http://office.microsoft.com/en-gb/excel-help/sumifs-function-HA010047504.aspx
0

Author Comment

Possibly but the die is cast as I've written and checked the SUMPRODUCT formulae. Thanks though.
0

Author Closing Comment

Solved problem, probbaly posted query a little too soon...
0

Featured Post

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…