Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Excel Lookup/Formula Expert Help Please

Posted on 2014-03-25
11
Medium Priority
?
204 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
Independent Software Vendors: 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 2000 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

719 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