Solved

Sumproduct

Posted on 2014-10-02
97 Views
Hi,

In sheet 1 i have two sumproducts not doing what i would hope they would do, i have highlighted in red the incoprrect answers and in green, what they should be, i know this will be a silly syntax error!

Thanks
EE.xlsx
0
Question by:Seamus2626
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 24

Assisted Solution

Phillip Burton earned 250 total points
ID: 40356878
``````=SUMPRODUCT(--(Data!I:I="ASP"),--(((Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"))*(Data!N:N="GBM")),Data!R:R)
``````
0

LVL 24

Accepted Solution

Phillip Burton earned 250 total points
ID: 40356880
At the moment, you are adding "90-100%" to "GBM", which makes 1+1=2; so you are getting twice the amount.
If you multiply it, then you will get 1*1=1.
0

LVL 51

Assisted Solution

Rgonzo1971 earned 125 total points
ID: 40356891
Hi,
pls try

in E13
=SUMPRODUCT(--(Data!I:I="ASP"),--(Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"),--(Data!N:N="GBM"),Data!R:R)
in F13
=SUMPRODUCT(--(Data!I:I="ASP"),--(Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"),--(Data!N:N="CMB"),Data!R:R)

The result will be 2.41 the 30 something is in 60-70% range

Regards
0

LVL 26

Expert Comment

ID: 40356908
here you go

put this in the red GBM

=SUMPRODUCT(Data!R:R,--ISNUMBER(MATCH(Data!I:I,{"ASP"},0)),--ISNUMBER(MATCH(Data!BH:BH,{"90-100%","80-90%"},0)),--ISNUMBER(MATCH(Data!N:N,{"GBM"},0)))
0

LVL 26

Expert Comment

ID: 40356913
and for CMB put this formula =SUMPRODUCT(Data!R:R,--ISNUMBER(MATCH(Data!I:I,{"ASP"},0)),--ISNUMBER(MATCH(Data!BH:BH,{"90-100%","60-70%"},0)),--ISNUMBER(MATCH(Data!N:N,{"CMB"},0)))
0

LVL 26

Assisted Solution

ProfessorJimJam earned 125 total points
ID: 40356919
check both formulas in the attached file. ready for you.
EE.xlsx
0

Author Closing Comment

ID: 40356941
Thanks guys!
0

Featured Post

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…
Suggested Courses
Course of the Month7 days, 11 hours left to enroll