Sum that returns the last running total if the "number1 and number2" cells are blank.
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)
Answers

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.

what is your formula in the Medication Available column?

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.

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

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.

Are you able to provide some screenshots for reference?

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)

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 nonblank 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 autonumber 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
Help Article Resources
Categories
Check out the Formula Handbook template!