Sum values of row up to same column as another row

1
votes

I'm trying to create a formula that will sum the values in a row, but only to the same column as the last non-zero column in another row.

image of columns and rows of data

So the formula at the end of row 1 (R1) should only sum columns C to H (Apr to sept) of row 1 because only C to H have data in row 2. October still has a value of zero in row 2.

But as soon as row two has data in column I (October) the formula in row 1 should now sum columns C to I (Apr to Oct)

3
excelexcel-formula
Like {=SUM(C1:H1*(C2:H2<>0))}? – Dirk Reichel
What have you tried? SUMIFS()? Or ..uh, what @DirkReichel said :D – BruceWayne
Hmm... how about Jun is 0 but Jul has a value? still add Jun to sum? – Dirk Reichel
I've been trying Sum ifs thus far. Dirk, I tried your formula but it returned a #VALUE! error – David Cartagena
=SUMIF(C39:N39,LOOKUP(1,1/(C39:N39>0),C39:N39),C39:N39) Started with this but still not getting what I need. i'm not sure where to reference the other row. – David Cartagena

3 Answers

1
votes

You can try to use sumif:

=SUMIF(C2:K2,">0",C1:K1)

This will sum row 1 only if row 2 is greater than 0. Hope that helps!

1
votes

To sum up all values until (including) the last non-zero value in row 2, you can use this:

=SUM(C1:INDEX(1:1,1,MAX((C2:H2<>0)*COLUMN(C2:H2),1)))

This is an array formula and must be confirmed with ctrl+shift+enter!

All other formulas till now only sum the values if row 2 is nonzero => if Apr and Jun are <>0 and May is 0, they will only sum Apr and Jun... My formula will do it for all 3 month ;)

1
votes

An alternative option

 =SUMPRODUCT(F9:O9*(F10:O10>0))

where F9:09 is your first row of data and F10:O10 is your second row of data

the above will work for positive values. If you want to also include negative values then use the following:

=SUMPRODUCT(F9:O9*(F10:O10<>0))

In light of Dirk's comments, he is absolutely correct. if you had a zero entry in the second row somewhere in the middle, the two formula's above would not count the values in the middle above the zeros. In order to correct and include values above zeros in the middle, use the following formula:

=SUM(F9:INDEX(9:9,1,AGGREGATE(14,6,(F10:N10<>0)*COLUMN(F10:N10),1)))

No CSE required as AGGREGATE performs array like operations for formula 14.