[Webinar] Streamline your web hosting managementRegister Today

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

# 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
• 4
• 3
• 2
• +1
1 Solution

Commented:
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

Commented:
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

Commented:
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

Commented:
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

Author 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

Author 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

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*")
0

Author 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

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

In my tests, it didn't double count.
0

Commented:
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

Author Commented:
SUPER
0

## Featured Post

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