Solved

Modifying an existing excel formula/ookup with more

Posted on 2014-03-25
3
177 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
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…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

863 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

23 Experts available now in Live!

Get 1:1 Help Now