Matt Pinkston
asked on
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","Upsi de"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'a ll 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))
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi
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))
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.
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.
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")+ISNUM BER(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
=SUMPRODUCT('all deals'!K:K,(('all deals'!H:H="Commit")+('all
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
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","Upsi de"},'all deals'!N:N,"Civilian")) + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'a ll deals'!N:N,"Civilian")-SUM (SUMIFS('a ll deals'!K:K,'all deals'!H:H,{"Commit","Upsi de"},'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
=SUM(SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi
That's because using {"Commit","Upside"} in SUMIFS returns an "array" of two values which need to be summed
regards, barry
ASKER
With
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi de"},'all deals'!N:N,"Civilian") + SUMIFS('all deals'!K:K,'all deals'!E:E,"*Forecast*",'a ll 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
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi
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
ASKER
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
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
=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*")
ASKER
=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
Is getting way to big a number, I think the <>Forecast is not right
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*",'a ll 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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
SUPER
SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi
=SUMIFS('all deals'!K:K,'all deals'!H:H,{"Commit","Upsi