Solved

Excel Lookup/Formula Expert Help Please

Posted on 2014-03-25
11
202 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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
 

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

632 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