Solved

Modifying an existing excel formula/ookup with more

Posted on 2014-03-25
3
172 Views
Last Modified: 2014-03-25
Here is my existing formula/lookup that works well.

=SUMIFS('all deals'!J:J,'all deals'!G:G,"Commit",'all deals'!M:M,"Civilian") + SUMIFS('all deals'!J:J,'all deals'!G:G,"Upside",'all deals'!M:M,"Civilian")

I also need to include from "all deals" when column M="Civilian" and column D includes somewhere in the string the word "forecast"
0
Comment
Question by:Matt Pinkston
  • 2
3 Comments
 
LVL 50

Expert Comment

by:barry houdini
ID: 39953281
You can simplify your current formula to a single SUMIFS like this:

=SUM(SUMIFS('all deals'!J:J,'all deals'!G:G,{"Commit","Upside"},'all deals'!M:M,"Civilian"))

Now you say you need to include when column M = "Civilian"......but that's already included so to add "Forecast" anywhere in column D just add a condition with wildcards like this:

=SUM(SUMIFS('all deals'!J:J,'all deals'!G:G,{"Commit","Upside"},'all deals'!M:M,"Civilian",'all deals'!D:D,"*Forecast*"))

regards, barry
0
 

Author Comment

by:Matt Pinkston
ID: 39953379
Barry is this what you had in mind?

=SUMIFS('all deals'!J:J,'all deals'!G:G,{"Commit","Upside"},'all deals'!M:M,"Civilian") + SUMIFS('all deals'!J:J,'all deals'!D:D,"*Forecast*",'all deals'!M:M,"Civilian")
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 39953509
Hello pinkstonmp

Can you restate the requirement?

You said


I also need to include from "all deals" when column M="Civilian" and column D includes somewhere in the string the word "forecast"

....but the "Civilian" requirement is already included, is it not? So I assumed that you want the current conditions but with an added condition for column D - the formula I posted is meant to do that - did it work for you?

regards, barry
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

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

743 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

12 Experts available now in Live!

Get 1:1 Help Now