[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel SUMPRODUCT query

Posted on 2013-06-18
13
Medium Priority
?
267 Views
Last Modified: 2013-06-23
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
Comment
Question by:CharAp
  • 7
  • 3
  • 2
  • +1
13 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39255675
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

by:
CharAp earned 0 total points
ID: 39255681
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

by:Steve
ID: 39255692
Are you using Excel 2003 or 2007+?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:CharAp
ID: 39255697
2007
0
 
LVL 85

Expert Comment

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

Author Comment

by:CharAp
ID: 39255704
It doesn't but ="Architect" does work
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 39255730
We'd need to see a sample then because the two should be the same.
0
 

Author Comment

by:CharAp
ID: 39255735
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

by:barry houdini
ID: 39255782
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

by:CharAp
ID: 39255804
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

by:Steve
ID: 39255867
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

by:CharAp
ID: 39255900
Possibly but the die is cast as I've written and checked the SUMPRODUCT formulae. Thanks though.
0
 

Author Closing Comment

by:CharAp
ID: 39269062
Solved problem, probbaly posted query a little too soon...
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question