Solved

Excel Lookup/Formula Expert Help Please

Posted on 2014-03-25
11
195 Views
Last Modified: 2014-04-11
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
Comment
Question by:Matt Pinkston
  • 4
  • 3
  • 2
  • +1
11 Comments
 
LVL 39

Expert Comment

by:nutsch
ID: 39954145
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
 
LVL 23

Expert Comment

by:NBVC
ID: 39954171
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 39954220
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
 
LVL 50

Expert Comment

by:barry houdini
ID: 39954230
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 Comment

by:Matt Pinkston
ID: 39954902
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:Matt Pinkston
ID: 39954905
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
 
LVL 39

Expert Comment

by:nutsch
ID: 39954931
=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 Comment

by:Matt Pinkston
ID: 39955351
=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
 
LVL 23

Expert Comment

by:NBVC
ID: 39955870
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39957005
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 Closing Comment

by:Matt Pinkston
ID: 39995457
SUPER
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now