Adding multiple cell values using check box true/false

[kares]

It's me again.

I am trying to add multiple cell values using the checkbox. As you can see in the attached file, I am trying to combine the cell values of those in the color red but I am having a rough time when I try to tick BOTH checkboxes as it doesn't calculate the total amount for all those in color red.

 

[saboh12617]

Hello,

I did not understand very well which checkbox corresponds to which value. However, you can multiply values by a boolean (TRUE/FALSE), as TRUE=1 and FALSE=0.

So in your screenshot, let's say if checkbox[C3-D3] corresponds to "adding values in A4:A6", and checkbox[C8-D8] to " adding values in A7:A8"
You can get the total via:

=SUM(A4:A6)*D3 + SUM(A7:A8)*D8

If you add many checkboxes,, you should use SUMPRODUCT on the same principle, adding a column of TRUE/FALSE next to the values to SUM.

 

[Joe4]

If you want to add both those together, your two IFs should be added together, not one imbedded inside the other, i.e. structured like this:

=IF(...) + IF(...)

If that is not what you are looking for, please let us know the expected value of your example.

 

[kares]

Apologies for the confusion, I was just so confused awhile ago.

So the context here is, when I tick the checkbox in C3 only, it should add the values in B2, A4 & A7.

Then if I tick the 2nd checkbox in C8 only, it should add B2 & A9 only.

the value in B2 is the primary value that needs to be added along with the other values that is in colored red.

Lastly, if I tick BOTH checkboxes, I would like to add all the cell values in B2, A4, A7 & A9.

 

[Joe4]

Try this formula:

=IF(D3+D8,B2,0)+(D3*(A4+A7))+(D8*A9)

In your example,
- if only D3 is TRUE, it returns 55
- if only D8 is TRUE, it returns 58
- if both D3 and D8 are true, it returns 63

Note that if you are only using the red text to show us what values should be added, this should work.
If the red text can change, and you want the formula to actually look at the color of the text to determine which cells to add, that is a whole different story, and we will need more information on what is turning the cells red.

 

[saboh12617]

Assuming the value of the checkboxes are in their respective cells, the formula below should work

=OR(C3,C8)*B2 + C3*(A4+A7) + C8*A9

If they are linked to the cells in D, then replace "C" by "D" in formula.

 

[kares]

Try this formula:

=IF(D3+D8,B2,0)+(D3*(A4+A7))+(D8*A9)

In your example,
- if only D3 is TRUE, it returns 55
- if only D8 is TRUE, it returns 58
- if both D3 and D8 are true, it returns 63

Note that if you are only using the red text to show us what values should be added, this should work.
If the red text can change, and you want the formula to actually look at the color of the text to determine which cells to add, that is a whole different story, and we will need more information on what is turning the cells red.

There is actually no code nor any formula used to turn those cell values into red. I just used red for emphasis. The attachment is like a dummy/test of what I want to achieve on the actual worksheet.

 

[kares]

Assuming the value of the checkboxes are in their respective cells, the formula below should work

=OR(C3,C8)*B2 + C3*(A4+A7) + C8*A9

If they are linked to the cells in D, then replace "C" by "D" in formula.

Thank you so much! This has solved my problem! Have a great day!

 

[kares]

Try this formula:

=IF(D3+D8,B2,0)+(D3*(A4+A7))+(D8*A9)

In your example,
- if only D3 is TRUE, it returns 55
- if only D8 is TRUE, it returns 58
- if both D3 and D8 are true, it returns 63

Note that if you are only using the red text to show us what values should be added, this should work.
If the red text can change, and you want the formula to actually look at the color of the text to determine which cells to add, that is a whole different story, and we will need more information on what is turning the cells red.

thank you! I just replaced "0" with B2 as I want its value to appear by itself when there are no checkboxes ticked.