subtract if tick box is ticked

[infamousurge]

hi excelforum members,

i got some very good help last year from you folks, so thought i'd test out your knowledge again...

it might be very simple... or it might not be possible, i just have no idea.

i've got a "battery club" spreadsheet in excel 2010. the user has to tick one of those 'wingdings' tickboxes if the battery has been sent. what i want to be able to do is calculate how many packs remaining calculated from the 'purchased' minus 'wingdings ticked'.

purchased 1st date 2nd date 3rd date 4th date 5th date 6th date remaining
12 18/04/2014 [ ] 25/04/2014 [ ] 02/05/2014 [ ] 09/05/2014 [ ] 16/05/2014 [ ] 23/05/2014 [ ] ???

is there an adaption of the countif formula that would work?

many thanks

infamousurge

[Tony Valko]

I think we're going to need to see a sample file that demonstrates what you're wanting to do.

Make it SMALL and show us what results you expect.

A SMALL file is one in which you don't have to scroll either vertically or horizontally to see the data!

[infamousurge]

ok cool, here goes

file is attached, the formula im looking for is the quantity remaining column

thanks

[Tony Valko]

Sorry, your file is too big for me.

[infamousurge]

Battery Club small.xlsx

is this better?

[Tony Valko]

Ok, that's better!

Looks like you're using ActiveX checkboxes.

You have to set the linked cell property then you can use a COUNTIF function to count how many checkboxes are "checked" or "unchecked". When a checkbox is checked the linked cell will display TRUE. If the checkbox is then unchecked the linked cell will display FALSE.

=COUNTIF(range,TRUE)
=COUNTIF(range,FALSE)

You probably don't want the user to see these cell entries so what you can do is set the font color of the cell to be the same as the background color which has the net effect of making the cell look empty/blank.

[infamousurge]

great, thank you. it seemed to work well, i was unaware you could link the checkboxes to the cells, so thanks.

my next question... is there a way of linking 2,000 check boxes to the cell that they are situated in, without doing it individually

thanks

[Tony Valko]

That's a lot of checkboxes!

You could probably do it with VBA but I'm not much of a programmer and don't know how to do it.

An alternative is to use an event macro that inserts an "X" in the cell when double clicked. Then there's no fooling around with linked cells. You'd count the cells with X's.

=COUNTIF(range,"X")

Again, I'm not much of a programmer and someone else would need to help with the code.

[popipipo]

An alternative is to use an event macro that inserts an "X" in the cell when double clicked. Then there's no fooling around with linked cells. You'd count the cells with X's.

Something like this; with this formula to count.
=COUNTIF(range,"p")

Please Login or Register  to view this content.

[Tony Valko]

I could have done it if the range was a contiguous range.

Now I see how you defined a non-contiguous range in your code.