Sum a range based on the status of a check box

Pete Oman

I am trying to create a fomula to add two columns based on whether a Check Box is ticked or not? I think this should be pretty easy but have not been able to figure out. I have tried using IF statement with no luck.

KrisWalsh

It can get confusing sometimes.  (:

 

Example Sheet - Right side

=IF(Ck1 = 1, ValueA1 + ValueB1, "")

Pete Oman

Kris, I appreciate the help. I actually got through it with some trial and error. I end up using =SUMIF(Status1, 1, [Quoted Amount]1 + [Change Order]1)

 

Thanks for your help.

Pete

Atus Bartal

Pete,

but it doesn't work either, does it? Why don't you use what Kris suggested (and can be seen on his example sheet)? Sumif()'s function is to summarize a range of cells (records) in a column (field) based on a criteria (the value of which varies in a range, too). Syntax: SUMIF(criteria_range, criteria_value, sum_range).

 

The right formula for your sheet may be this one (just delete SUM from the beginning): =IF(Status1, 1, [Quoted Amount]1 + [Change Order]1), copied to all lines. 

Or, if you want to have only ONE checkbox and one cell for the sum value (for instance on the top of the sheet), this would also work (for 99 lines): =IF(Status1, SUM([Quoted Amount]1:[Change Order]99), ""). I'm not sure what kind of solution you are looking for. 

Please, keep us informed

Atus

 

 

 

 

J. Craig Williams

Kris,

 

This

 

=IF(Ck1 = 1, ValueA1 + ValueB1, "")

 

is the same as

 

=IF(Ck1, ValueA1 + ValueB1, "")

 

Craig