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
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The final part of your formula confused me, but, after making my assumptions, I ended up with this:
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)?
=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)?
ASKER
The amounts to be added are in column G of Sheet1.
ASKER
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
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?
ASKER
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.
=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))ASKER
Thank you all for your help on this one.
Conernesto
Conernesto
ASKER
On your formula, what does B1 represent?