CODEWITHSUNDEEP
if-statementgoogle-sheetssumgoogle-sheets-formulasumifs
Infamoose
Infamoose

Reputation: 33

Deduct an integer from another integer every time a checkbox is ticked

I'm currently working on a sheet that has a "Total" stat on it which will represents slots and I want that total slots number to be deducted by "1" every time a tick box is checked.

For example, there are 8 total slots and there are 8 Tick Boxes, each time a slot is consumed, a box is ticked and the total remaining slots is deducted by 1.

What would be the best way of making this happen? I understand I can enable "iterative calculations" and have something like

=SUM(F15,IF(F15>0,SUM(IF(K15,-1,0)+IF(L15,-1,0)+IF(M15,-1,0)+IF(N15,-1,0),0)))

I'm a total spreadsheet noob but there has to be a more consolidated way to do it.

[Example image of total with tick boxes.]1

Upvotes: 1

Views: 90

Answers (2)

Osm
Osm

Reputation: 2881

Use this formula

=ArrayFormula(COUNTA(B1:E2)-SUM(IF(B1:E2=TRUE,1,0)))

enter image description here

Named functions - why not

Go to: Data > named functions

Lets name the function DEUCECOUNTA and paste this in formula definition

=ArrayFormula(COUNTA(range)-SUM(IF(range=TRUE,1,0)))

Paste "range" this in the argument place holders and press enter.

Use it like this

=DEUCECOUNTA(B1:E2)

enter image description here

Demo

enter image description here

Upvotes: 0

player0
player0

Reputation: 1

try this:

=8-SUMPRODUCT(B1:E2)

enter image description here

Upvotes: 1

Related Questions