SUMMING SUMIFS's

[HangMan]

I want to be able to SUMIF, five SUMIFS statments. The total of each SUMFS statement only gets included in the total if it is >0 are >0, so if the result of any individual SUMIFS statement is <0, it doesn't get included in the main SUMIF total.

So, if

=SUMIFS($AS$3:$AS$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648) were to equal 120
=SUMIFS($AY$3:$AY$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648) were to equal -250
=SUMIFS($BE$3:$BE$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648) were to equal 32
=SUMIFS($BK$3:$BK$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648) were to equal 152
=SUMIFS($BR$3:$BR$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648) were to equal 48

Then I need a SUMIF that would return 352, because the 2nd SUMIF statement results in a value <0, it doesn't gt included in the total. I don't seem to be able to SUMIF several SUMIFS statements. Is there another way to do this? I'm trying to avoid using an IF statement, because I just need the total to then use in a larger formula and I don't have true and false outcomes.

Many thanks

[Jonmo1]

Sometimes it's best to NOT combine multiple calculations into 1 formula..

If you have those 5 formulas already working in a range..Say those formulas are in A1:A5 for example.
Then just do a simple
=SUMIF(A1:A5,">0")

[HangMan]

Sadly, it's not that simple. Each SUMIFS Statement is totalling a certain number of rows in each of the five columns, based on the criteria in two other columns, so these are effectively running totals going down around 5,000 rows. I need to be able to SUM the total of the five coumns and intervals, as mentioned above. It isn't a fixed outcome at any point, if that makes sense, so unfortunately =SUMIF(A1:A5,">0") wouldn't work in this case.

[Jonmo1]

Can you post a sample file with just say 20 rows or so with expected results?

[Pauleyb]

I'm confused by this statement

I'm trying to avoid using an IF statement, because I just need the total to then use in a larger formula and I don't have true and false outcomes

It seems like an IF statement is needed somewhere, and I don't see how you want the formula to ignore sums <0 in one cell, then not ignore them in the other. That implies they are different formulas, so why worry if one has IFs in it and the other does not?

One reason I can think of is that you want to have a base equation that sums them all, and that equation changes everytime you get new data. You want to use that base, and easily cut/paste it into the one that ignores the <0 results. Is it something along those lines?

If so, or something like that, then you could use something like this. The equation would get pretty hairy and long, but it should work.

Let's call your base equation, which does the sum of all of the ranges as "X". X would be something like =SUMIFS(...)+SUMIFS(...)+SUMIFS(...)+....
Now you would need an equation Y that uses the X formula and adds to it. The first part of Y would be the same as X, so just copy and paste the text. The second part of Y is added to the first part and would be a conversion of all of the SUMIFS() to ABS(SUMIFS()). This can be achieved by again pasting X and then selecting the text and Find/Replace 'SUMIF(' with 'ABS(SUMIF(' and each ')+' with '))+'. You may need a word editor for this as I'm not sure if Excel can find/replace only selected text within a formula. Finally, with that big sum, you divide it by two.

Not a great solution, I know, but I'm trying to avoid the use of IF.

[HangMan]

The formula I need to adjust is in Column D:

=IFERROR(($C3/SUMIFS($C$3:$C$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3))*(SUMIFS($C$3:$C$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3)/SUMIFS($I$3:$I$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3))*SUMIFS($J$3:$J$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3),0)

The part I need to change is highlighted in red. Instead of this being the SUM of Column I, which includes all negative values, when Column A = Surrey and Column B = Blue, I need this to to be the total of Columns 1 through 5, if Column A = Surrey and Column B = Blue, but only if the total of each Column > 0.

In this example, for Surrey:

Column, 1 = 858
Column, 2 = 0
Column, 3 = 1,821
Column, 4 = -763
Column, 5 = 723,

So for the section in the formula above in red, I need the total of Columns 1 through 5, but only if each individual Column is >0, so in this case, the total for Columns 1, 2, 3 and 5, which equals 3,402. The negative figure in Column 4 must not be included in the total at all.

Hence my need to SUM a number of SUMIFS's

I hope that makes a bit more sense.

Many thanks

[HangMan]

Hi PaulyB,

Hopefully my last post and the sample will help to explain things a little more clearly.

Many thanks

[Jonmo1]

OK, so the same answer.

Put 5 sumifs formulas in available columns, Say K:O in the book you posted.

K3: =SUMIFS($C$3:$C$4372,$A$3:$A$4372,$A3,$B$3:$B$4372,$B3)
L3: =SUMIFS($E$3:$E$4372,$A$3:$A$4372,$A3,$B$3:$B$4372,$B3)
M3: =SUMIFS($F$3:$F$4372,$A$3:$A$4372,$A3,$B$3:$B$4372,$B3)
N3: =SUMIFS($G$3:$G$4372,$A$3:$A$4372,$A3,$B$3:$B$4372,$B3)
O3: =SUMIFS($H$3:$H$4372,$A$3:$A$4372,$A3,$B$3:$B$4372,$B3)

Then your formula in D3 is
=IFERROR(($C3/SUMIFS($C$3:$C$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3))*(SUMIFS($C$3:$C$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3)/SUMIF($K3:$O3,">0"))*SUMIFS($J$3:$J$162,$A$3:$A$162,$A3,$B$3:$B$162,$B3),0)

[HangMan]

Hi Jonmo1,

Thanks for your reply... I really need one of the five columns to be the column with the formula in because this is the format of the spreadsheet and this can't be changed. In reality, there will is a Column to the right of each of the five Columns, in the same way as Column D, being Adjacent to Column C, each of which will do the same job or apportioning costs on a proportinal basis, so it is'nt going to be possible to add additional columns in the way suggested. Is there anyway that your suggestion can be adapted to include the actual column containing the formula, if that makes any sense?

So, in my sample sheet there would be Column 1, then Column 1 Apportionment, Column 2, Column 2 Apportionment and so on... Each apportionment column will need to contain the formula and therefore be one of the five you suggest above.

Many thanks

[Pauleyb]

Hopefully my last post and the sample will help to explain things a little more clearly

Yep, it clearly shows you have a difficult nut to crack.

First, let me say, Jonmo's solution is probably the best one. I don't understand why you can't use other columns - are you truly using the columns all the way up to column XFD?

SUMIF and SUMIFS require a range to be summed. A group of SUMIFS outputs is not a range.

Here is a possible solution.

Use name manager to 'compartmentalize' each of those sumifs. So:
Group1 =SUMIFS($AS$3:$AS$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648)
Group2 =SUMIFS($AY$3:$AY$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648)
Group3 =SUMIFS($BE$3:$BE$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648)
Group4 =SUMIFS($BK$3:$BK$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648)
Group5 =SUMIFS($BR$3:$BR$4372,$G$3:$G$4372,$G648,$CI$3:$CI$4372,$CI648)

Then your RED section above becomes:
SUM(Group1*(--(Group1>0)),Group2*(--(Group2>0)),Group3*(--(Group3>0)),Group4*(--(Group4>0)),Group5*(--(Group5>0)))

This is more for readability. You could replace all of those Groups with the actual SUMIF, but that would be painful to decode. Note that I am not using an IF statement, since you don't want to do that, but it could be changed to:
IF(Group1>0,Group1,0)+IF(Group2>0,Group2,0)+...

[Pauleyb]

Also, it looks like your formula can be reduced. It is essentially:
(A/B)*(B/C)*D
which can be reduced to
D*A/C