Modifying an existing excel formula/ookup with more

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"
Matt PinkstonAsked:
Who is Participating?
 
barry houdiniCommented:
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
 
barry houdiniCommented:
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
 
Matt PinkstonAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.