[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 208
  • Last Modified:

Excel Lookup/Formula Expert Help Please

I have the following formula which I believe is double dipping numbers

=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")

The requirement should be:

Give me the total of all deals that have column (N=Civilian and (H=Commit or Upside, OR E=Contains Forecast))
0
Matt Pinkston
Asked:
Matt Pinkston
  • 4
  • 3
  • 2
  • +1
1 Solution
 
nutschCommented:
Subtract
SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian",'all deals'!E:E,"*Forecast*")

=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")-SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian",'all deals'!E:E,"*Forecast*")
0
 
NBVCCommented:
I am not sure about that.

I tested using your original formula and it didn't seem to double dip.

It gave a result of unique rows matching either of the criteria.

So if E contains Forecast and H is Commit, it would still count as 1 match.
0
 
barry houdiniCommented:
If you want an OR the way you say then I don't think you can do that easily with SUMIFS - try SUMPRODUCT like this:

=SUMPRODUCT('all deals'!K:K,(('all deals'!H:H="Commit")+('all deals'!H:H="Upside")+ISNUMBER(SEARCH("forecast",'all deals'!E:E))>0)*('all deals'!N:N="Civilian"))

The K column is summed if N = "Civilian" and any one or more of the other conditions is fulfilled for columns H or E

although it will be quicker to use a specific range rather than whole columns

regards, barry
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
barry houdiniCommented:
Thomas, for your suggestion to work you need to wrap the first and third SUMIFS in a SUM function, i.e.

=SUM(SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian")) + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")-SUM(SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian",'all deals'!E:E,"*Forecast*"))

That's because using {"Commit","Upside"} in SUMIFS returns an "array" of two values which need to be summed

regards, barry
0
 
Matt PinkstonAuthor Commented:
With
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")

I could have had deals that met the Commit or Update and had Forecast in column E The it would be double counted for the second part
0
 
Matt PinkstonAuthor Commented:
So what the logic really needs to be is

total of all deals that have column (N=Civilian and (H=Commit or Upside AND E= does NOT Contain Forecast))

PLUS
 
total of all deals where column E Contains Forecast
0
 
nutschCommented:
=SUMIFS('all deals'!K:K,'all deals'!H:H,"Commit",'all deals'!N:N,"Civilian",'all deals'!E:E,"<>Forecast" ) + SUMIFS('all deals'!K:K,'all deals'!H:H,"Upside",'all deals'!N:N,"Civilian",'all deals'!E:E,"<>Forecast" ) +SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*")
0
 
Matt PinkstonAuthor Commented:
=SUMIFS('all deals'!K:K,'all deals'!H:H,"Commit",'all deals'!N:N,"Civilian",'all deals'!E:E,"<>Forecast" ) + SUMIFS('all deals'!K:K,'all deals'!H:H,"Upside",'all deals'!N:N,"Civilian",'all deals'!E:E,"<>Forecast" ) +SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*")

Is getting way to big a number, I think the <>Forecast is not right
0
 
NBVCCommented:
With
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")

I could have had deals that met the Commit or Update and had Forecast in column E The it would be double counted for the second part

In my tests, it didn't double count.
0
 
barry houdiniCommented:
Hello Pinkstonmp, did you try either of my suggestions above? - I think they both do what you are asking....although the SUMIFS version can be shortened, i.e.

=SUM(SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upside"},'all deals'!N:N,"Civilian",'all deals'!E:E,"<>*Forecast*"))+SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'all deals'!N:N,"Civilian")

or this version with SUMPRODUCT

=SUMPRODUCT('all deals'!K:K,(('all deals'!H:H="Commit")+('all deals'!H:H="Upside")+ISNUMBER(SEARCH("forecast",'all deals'!E:E))>0)*('all deals'!N:N="Civilian"))

With both of those the K column is summed if N = "Civilian" and any one or more of the other conditions is fulfilled for columns H or E

They should both get the same results

regards, barry
0
 
Matt PinkstonAuthor Commented:
SUPER
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now