Sum only IF a cell has value but have it be cumulative if the previous cell is blank

[gfl0101]

Hello,

I would like to have a formula in Column B which only shows a value if column A has a value (other than 0) but would like Column B to be cumulative.

For example if in Column A, lines 13,14,15,16 are zero but line 17 in Column A has a value, then in column B line 17 would be my value in C1 times 5 (to account for lines 13,14,15,16, and 17).

I tried creating a formula with IF statements which I have attached but I only accounted for three zeroes in Column A, I would like to see if there is a range formula or a more simplistic formula for this.

Thank you.

[jomili]

If A is a Blank

Please Login or Register  to view this content.

If A = 0 or is blank

Please Login or Register  to view this content.

[gfl0101]

Thanks for the reply but I don't want to take the sum column A. I only want to the sum of column A until the last value above zero and then multiplied by C1, so it would look something like this. Assume C1 = 2.

Column A Column B
0 0
5 4
0 0
3 4
0 0
0 0
0 0
0 0
0 0
6 12

[Glenn Kennedy]

Please amend your ORIGINAL sheet, to show an EXTRA column with manually calculated results.

[José Augusto]

Try this in B2 and copy down

Please Login or Register  to view this content.

[jomili]

I don't understand your explanation with your example. The last value above zero in A is 6, multiplied by 2 is 12,so I get that part. But I don't understand the 4's in column B.

[davsth]

I think in your example spreadsheet you need to post what values you would like the formula to produce. Then we can write a formula to do it. Switching between an example attached sheet that should be showing what you want and then text in the conversation, is not conducive to getting an answer.

[gfl0101]

Please find the revised file attached.

[gfl0101]

This is good, thank you! However it doesn't work in B1 and B2.

What do the 14, 6 represent?

[José Augusto]

See this formula in B2 and copy down

Please Login or Register  to view this content.

Comments:
In the AGGREGATE function 14 means Greater and 6 means to ignore errors.

This function gives you the row of the las non zero after the row you have the formula

[davsth]

if you can live with an extra line at the top the attached array formula works

[gfl0101]

Thank you all!
Could you please check the formula in cell E11, it should be 2 not 22, not sure what I am doing work. Thanks.

[davsth]

2 or 4

my formula wants a value in e9 or e10 depending on what you require, hence the extra line. it searches for the previous non 0 row

[gfl0101]

sorry my typo, correct 4 not 2.

[gfl0101]

I added another condition, if you could please take a look at the attachment. Thank you.

[gfl0101]

bump..just need help fixing the 22 instead of 4 issue. The other condition does need to be taken into consideration anymore. thanks

[Glenn Kennedy]

Try adding any text as a header at K9. It took a while to track it down!!

[gfl0101]

Solved, thank you very much Glenn!

[José Augusto]

HI @gfl0101

In @davsth and @José Augusto solutions you must have headers in C9 and E9.

See the file

[Glenn Kennedy]

You're welcome.

---

If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.

It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.