SUMIFS with various criterias

I have a workbook with two tabs. The first tab is named “2015 TrialBalanace”. The second tab is named “Sheet1”. I need an Excel formula on tab 2015 TrialBalanace Cell G14 that does the following:
Tab 2015 TrialBalanace has a value on cell F2, cell A14 and cell G56 . I need a formula that adds the amounts on Sheet1 column G if Sheet1 column B is equal to 2015 TrialBalanace cell F2, If Sheet1 column F is equal to 2015 TrialBalanace cell A14, If Sheet1 column C is equal to 2015 TrialBalanace cell G56, if Sheet1 column E is equal to 6411090 then multiply Sheet1 column G times mines 1, else column G.
SumIFS_4-criterias.xlsx
ConernestoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike in ITIT System AdministratorCommented:
Based on how I'm understanding your requirements you can use a formula like this:
=IF(AND(VALUE(B1)=VALUE(TrialBalanceYear),C1=TrialBalanceCode,F1=TrialBalanceNumber),IF(E1=6411090,G1*-1,G1),"False")

Open in new window


I used Defined Names to make things easier, this way you can write the formula and then just copy it down.
F2 = TrialBalanceYear
G56 = TrialBalanceCode
A14 = TrialBalanceNumber

I added the false just for easier viewing.
0
byundtMechanical EngineerCommented:
The brute force way of writing the formula is to use a SUMIFS for the first three criteria, then subtract twice the SUMIFS for the four criteria.
=SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56)-2*SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56,Sheet1!$E:$E,6411090)

Open in new window

The formula subtracts the four criteria SUMIFS once because it was adding those values in the first SUMIFS, and then again to give effect to the requirement of multiplying those values by minus 1.

If you find subtracting twice the four criteria SUMIFS too confusing, a slightly longer formula uses two four criteria SUMIFS. The first one includes only those rows where column E is not equal to 6411090, while the second subtracts those rows where column E does equal 6411090.
=SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56,Sheet1!$E:$E,"<>6411090")-SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56,Sheet1!$E:$E,6411090)

Open in new window

Both formulas give the same answer, so take your pick.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ConernestoAuthor Commented:
Hi Mike,

On your formula, what does B1 represent?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richard DanekeTrainerCommented:
The final part of your formula confused me, but, after making my assumptions, I ended up with this:

=SUMIFS(Sheet1!G1:G18,Sheet1!B1:B18,'2015 TrialBalance'!F2,Sheet1!F1:F18,'2015 TrialBalance'!A14,Sheet1!C1:C18,'2015 TrialBalance'!G56)+IF(SUM(Sheet1!E1:E18)=6411090,SUM(Sheet1!E1:E18)*-1,SUM(Sheet1!E1:E18))

Open in new window


After the conditional sums, one is supposed to add the total of column E unless it is 6411090, then one adds the sum of column E time minus 1 (-1)?
0
ConernestoAuthor Commented:
The amounts to be added are in column G of Sheet1.
0
ConernestoAuthor Commented:
byundt,

Your first formula works very good. I will try the second formula when I have time. How would the first formula that you sent me be modified if I need another condition. On tab named 2015 TrialBalance, I have values on cell G57. I need the condition that if G57 is equal to "5471" to be part of the formula.
0
Mike in ITIT System AdministratorCommented:
B1 is the value in Column B row 1. It is what you said should be compared to F2 on the 2015 Trial Balance sheet.
0
byundtMechanical EngineerCommented:
I see cell G57, but don't understand how you want to modify the formula.

Since I do not see 5471 in one of the columns on Sheet1, my guess is that it would not appear as a criteria inside the SUMIFS. More likely, you want to use an IF function testing whether G57=5471. What should be the result (calculation) if G57 is 5471? What should be the result if it is not 5471?
0
ConernestoAuthor Commented:
If G57 is not equal to 5471, the result should be zero. If G57 is equal to 5471, do everything that is in the SUMIFS first formula that you sent me.
0
byundtMechanical EngineerCommented:
=IF(G57<>5471,0,SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56)-2*SUMIFS(Sheet1!$G:$G,Sheet1!$B:$B,F2,Sheet1!$F:$F,A14,Sheet1!$C:$C,G56,Sheet1!$E:$E,6411090))

Open in new window

0
ConernestoAuthor Commented:
Thank you all for your help on this one.

Conernesto
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.

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.