• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

Sumproduct

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
Seamus2626
Asked:
Seamus2626
4 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
=SUMPRODUCT(--(Data!I:I="ASP"),--(((Data!BH:BH="90-100%")+(Data!BH:BH="80-90%"))*(Data!N:N="GBM")),Data!R:R)

Open in new window

0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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
 
Rgonzo1971Commented:
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
Technology Partners: 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!

 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
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
 
ProfessorJimJamCommented:
check both formulas in the attached file. ready for you.
EE.xlsx
0
 
Seamus2626Author Commented:
Thanks guys!
0

Featured Post

Independent Software Vendors: 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!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now