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

  • A T
    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
    Leibel S ✭✭✭✭✭✭

    what is your formula in the Medication Available column?

  • A T
    A T ✭✭

    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
    Leibel S ✭✭✭✭✭✭

    @A T

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

  • A T
    A T ✭✭

    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
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide some screenshots for reference?

  • A T
    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)


  • 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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!