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

CodePudding user response：

try this:

=8-SUMPRODUCT(B1:E2)

CodePudding user response：

Use this formula

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

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)

Demo