# 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
###### Who is Participating?

x
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.

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")

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.
Mechanical 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)
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)

Experts Exchange Solution brought to you by

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

Author Commented:
Hi Mike,

On your formula, what does B1 represent?
TrainerCommented:
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))

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)?
Author Commented:
The amounts to be added are in column G of Sheet1.
Author 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.
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.
Mechanical 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?
Author 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.
Mechanical 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))
Author Commented:
Thank you all for your help on this one.

Conernesto
###### 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.