Sum that returns the last running total if the "number1 and number2" cells are blank.

A T · 07/17/22

I have a Master sheet that keeps a running total based off of a separate Transaction sheet.

The running total on the master is referencing a cell in the transaction sheet that is currently a basic SUM of the 2 cells next to it. I need a function that will keep this SUM function intact BUT if the cells in the SUM are blank return the most recent total from previous transactions.

Example: Transaction for Box A - entry showed 1 available(available column), 2 were added(add/remove column), total is 3(total column).

Second transaction for Box A - entry showed nothing in the available and added boxes but I want this to return a total of 3 still.

Third transaction for Box A - entry showed 3 available, 2 added, total now 5

In this instance I would want the Master sheet to have shown a running total for Box A as 3 after the first transaction, still 3 after the second transaction, and 5 after the third.

As it stands now the Master sheet will show 3, 0, and then 5 because the function returning the totals is just based off of a basic SUM function.

TRANSACTION SHEET TOTAL FORMULA =SUM([Medication Add/Remove]@row:[Medication Available]@row)

A T · 07/17/22

Looking through other posts I'm thinking something like this

=IF(COUNT(Likelyhood@row:Proximity@row) < 1, "", SUMIF(Medication Available@row:Mediation Add/Remove@row, <>""))

But the return if less than 1 needs to be the most recent known total for the location selected on that row.

Leibel S · 07/18/22

what is your formula in the Medication Available column?

A T · 07/18/22

https://community.smartsheet.com/discussion/comment/336436#Comment_336436

It is an input from a form but would not be collected during every submission because in some instances it is just an input that a lockout tag is still intact. Thus the previous amount available would still be accurate.

Leibel S · 07/18/22

@A T

shouldn't the total of 'Mediation Add/Remove' column be the total available?

A T · 07/18/22

https://community.smartsheet.com/discussion/comment/336442#Comment_336442

Not necessarily since it's a running count and the available is referencing your starting total before any transaction. Say you go to open the box to do a count. There are 5 of medication A in the box, you didn't add or remove any, so the available=5, add/remove=0, total=5. But if you go to take out 1 of medication A then it would be available=5, add/remove=-1,total 4.

Paul Newcome · 07/19/22

Are you able to provide some screenshots for reference?

A T · 07/20/22

This is the Master sheet where the totals are displayed

Function for the Available column - =INDEX({3B Ativan Available}, MIN(COLLECT({3B Row#}, {3B Narc Box}, [Box Number]@row, {3B Ativan Available}, @cell <> "")))

This is the transaction log

Function so far for the total column is just - =SUM([Ativan Available]@row:[Ativan Add/Remove]@row)

Genevieve P. · 08/02/22

Hi @A T

Thank you for these images!

Would it be possible that you could have two in a row that are blank? So instead of looking to the previous "row" you would want to look to the previous non-blank row?

In this case, I have a potential solution here. Perhaps some of the other formula wizards may have a cleaner solution, but this worked for me!

What I would do is set up an auto-number column in your sheet so that your rows have a number associated, then use the MATCH function to bring the row number into a cell, BUT only if the two cells are not blank:

=IF(AND([Ativan Available]@row <> "", [Ativan add/remove]@row <> ""), MATCH([Row ID]@row, [Row ID]:[Row ID]))

Screen Shot 2022-08-02 at 10.58.58 PM.png

This is so we can find the "Max" row number where the cells aren't blank and use that row to return the Available cell data and the add/remove cell data, based on the Box.

Here's the structure of the Total formula:

=IF([Row Number]@row = "", Ativan Available formula + Ativan add/remove formula, SUM([Ativan Available]@row:[Ativan add/remove]@row))

In the two italic places, we'll have two different INDEX formulas. For example, here's how we can bring back the Ativan Available data from a previous row:

INDEX([Ativan Available]:[Ativan Available], MAX(COLLECT([Row Number]:[Row Number], [Narc Box]:[Narc Box], [Narc Box]@row)))

We can use the same thing but looking at the add/remove column:

INDEX([Ativan add/remove]:[Ativan add/remove], MAX(COLLECT([Row Number]:[Row Number], [Narc Box]:[Narc Box], [Narc Box]@row)))

To then replace my italic text with each of these INDEX formulas for a full formula:

=IF([Row Number]@row = "", INDEX([Ativan Available]:[Ativan Available], MAX(COLLECT([Row Number]:[Row Number], [Narc Box]:[Narc Box], [Narc Box]@row))) + INDEX([Ativan add/remove]:[Ativan add/remove], MAX(COLLECT([Row Number]:[Row Number], [Narc Box]:[Narc Box], [Narc Box]@row))), SUM([Ativan Available]@row:[Ativan add/remove]@row))

Screen Shot 2022-08-02 at 11.02.11 PM.png

Let me know if this brings back your intended result!

Cheers,

Genevieve

Sum that returns the last running total if the "number1 and number2" cells are blank.

Answers

Help Article Resources

Categories