Making a Formula Contingent on Check Boxes

[mwheelock]

I'm trying to make a formula that subtracts the correct surcharges
when checked off. For example:

=IF(G5>0,G5-SUM(F9,F10,F11,F12,F13,F14,F15,F18,F20,F21,F22))


I would like each of the F cells to only populate the formula when a
check box is checked off next to them. Does anyone know how I would
go about something like that? I believe it would have something to
do
with the Forms or ActiveX function. Any help would be greatly
appreciated. Thanks.

 

[Sandy Mann]

You don't say where you mean by

check box is checked off next to them

but assuming that you mean the cell immediately to the right (ie Column G)
then try:

=IF(G5>0,SUMPRODUCT(((G9:G15<>"")*(F9:F15)))+((G18<>"")*(F18))+SUMPRODUCT(((G20:G22<>"")*(F20:F22))),0)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[T. Valko]

Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G5>0,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

 

[Atiq]

hi, i am facing similar problem and would really appreciate if you can post
an example here.

thanks,
Atiq

 

[mwheelock]

Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G5>0,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

I don't know too much regarding checkboxes. I'm going to try to
figure it out today. I'm just a little unclear with respect to the
cell linking. I'll research the help and see what I come up with
although an example would help. Thanks!

 

[mwheelock]

Ok, how much do you know about creating checkboxes?

Assuming column F contains numeric values and you create checkboxes in the
corresponding cells in column G. What you have to do is link each checkbox
to a cell. This linked cell will display the status of each checkbox, either
TRUE or FALSE, TRUE when the checkbox is checked and FALSE when the
checkbox is not checked.

Then you can get the sum of surcharges by summing those cells where the
corresponding linked cell = TRUE.

Your formula might look like this:

=IF(G5>0,G5-SUMIF(G9:G22,TRUE,F9:F22),"")

Post back if you need more assistance or if it'll help, I'll put together a
small sample file that demonstrates this.

--
Biff
Microsoft Excel MVP

<

Actually I figured it out. Thanks so much for your help with this!