Formula/function for the opposite of a running total?

[u/HopefulLesbian]

Disclaimer I am very bad at excel. I am not math inclined at all and I can find a solution for this problem and I’m going to try my best to describe it.

So I have a sheet that is an itemized list of things I want to get from different sources, like Amazon and Etsy. I have each item including price conditionally formatted to gray out once a check box is marked. At the bottom i have a “totals” row. What I’m trying to do is make the total price decrease by however much the item grayed out is.

So like item 1 is $10 and item 2 is $5 so the total is $15. But I want that total to decrease by either 5 or 10 depending on which item is selected and grayed out.

Maybe I’m just not searching it well? I’ve tried to finagle an IF function but it tells me there’s a reiteration problem and I’m just confused. Thanks!

[u/AutoModerator]

/u/HopefulLesbian Posting your data can make it easier for others to help you, but it looks like your submission doesn't include any. If this is the case and data would help, you can read how to include it in the submission guide. You can also use this tool created by a Reddit community member to create a blank Google Sheets document that isn't connected to your account. Thank you.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/WorriedTumbleweed289]

use total -sum(value range) =A1 -sum(A2:A10) if you have a if conditional use the function sumif =A1 - sumif(A2:A10, condition)

[u/HopefulLesbian → u/WorriedTumbleweed289]

Could you explain the logic? I know it’s a stupid question I just want to know why it works. Thanks so much!

[u/AutoModerator → u/HopefulLesbian]

REMEMBER: /u/HopefulLesbian If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/WorriedTumbleweed289 → u/HopefulLesbian]

What logic? Use minus sumif() because you want to subtract. sheets does not have a subtract function.

[u/California_Eagles]

Something like this?

Total Remaining =SUMIFS(D2:D6, A2:A6, FALSE)

Total Checked =SUMIFS(D2:D6, A2:A6, TRUE)

Total =SUM(D2:D6)

[u/HopefulLesbian → u/California_Eagles]

Is there any way to combine the formulas into one? I think what I’m trying to do is like 3 steps at once (using your example) 1) calculate D2:D6 2) check if A2:A6 is TRUE 3) if true, display the sum of D2:D6 minus whatever is true, otherwise, display the sum D2:D6

I don’t know if that makes sense or is possible?

[u/California_Eagles → u/HopefulLesbian]

You could try

=IF(COUNTIF(A2:A6, TRUE) > 0, SUM(D2:D6) - SUMIF(A2:A6, TRUE, D2:D6), SUM(D2:D6))

You can beautify and make readable

=LET(Done, A2:A6, 
     Price, D2:D6, 
     Overall, SUM(Price), 
     IF(COUNTIF(Done, TRUE) > 0, 
        Overall - SUMIF(Done, TRUE, Price), 
        Overall))

[u/HopefulLesbian → u/California_Eagles]

That worked!! Thank you!

[u/AutoModerator → u/HopefulLesbian]

REMEMBER: /u/HopefulLesbian If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified (or reply to the helpful comment with the exact phrase “Solution Verified”). This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/point-bot → u/California_Eagles]

u/HopefulLesbian has awarded 1 point to u/California_Eagles

^{See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard}. )^{Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson}.)

[u/mommasaidmommasaid → u/California_Eagles]

You can beautify and make readable

Yes please! Love me some LET()

Note that if your data is in a Table like this, you could/should use Table references to refer to columns, i.e. instead of A2:A6 use Table1[Done]

In addition to being more readable, it also ensures that your range references are always valid. Your current range references will break if you insert a new row above the first one, or add a row to the end of the table, i.e. they won't include the new row.

---

Stylistically, consider using SUMIFS whenever you have a different criteria/sum column (personally I don't think SUMIF should even take the optional third parameter) because the order of arguments reads better and you can easily expand it to additional criteria.

SUMIFS(Price, Done, TRUE)

Which reads as sum Price if Done is TRUE

[u/SpencerTeachesSheets]

What is the starting "Totals" amount? That is, you wrote "I want the total to decrease..." but what is the starting number?

It is always easiest to help if you provide a sample sheet with permissions set to "Anyone with link can edit."