CODEWITHSUNDEEP
excelexcel-formula
David Cartagena
David Cartagena

Reputation: 11

Sum values of row up to same column as another row

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)

Upvotes: 1

Views: 344

Answers (3)

Forward Ed
Forward Ed

Reputation: 9874

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.

Upvotes: 1

Dirk Reichel
Dirk Reichel

Reputation: 7979

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

Upvotes: 1

Kathy
Kathy

Reputation: 11

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!

Upvotes: 1

Related Questions