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

A T

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

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

what is your formula in the Medication Available column?

A T

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

@A T

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

A T

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

Are you able to provide some screenshots for reference?

A T

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.

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

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

Let me know if this brings back your intended result!

Cheers,

Genevieve