Solved

Excel SUMPRODUCT query

Posted on 2013-06-18
13
221 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
Comment Utility
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
Comment Utility
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
Comment Utility
Are you using Excel 2003 or 2007+?
0
 

Author Comment

by:CharAp
Comment Utility
2007
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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
Comment Utility
It doesn't but ="Architect" does work
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 85

Expert Comment

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

Author Comment

by:CharAp
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Possibly but the die is cast as I've written and checked the SUMPRODUCT formulae. Thanks though.
0
 

Author Closing Comment

by:CharAp
Comment Utility
Solved problem, probbaly posted query a little too soon...
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

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…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

763 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now