Forum Discussion
Excel: SUM and SUMIF
Hi all, I am preparing an excel file and I have an issue with the SUM formula.
In the file, I added a checkbox that will give 0 if FALSE (not ticked) or 125 is TRUE (ticked).
For this I used the formula =SUMIF(K21,TRUE,M21) where:
K21 is the true/false cell linked to the checkbox
M21 is 125
What I want to achieve is: When the box is ticked, add 125 to the final amount (in cell J37) and sum all prices in cells J26:J35 (and when the checkbox is not ticked, then 125 should not appear in the total sum).
Both formulas:
=SUMIF(K21,TRUE,M21)
=SUM(J26:J35)
Work separately, but they do not work together in the same cell, as I would like to have just one final cell (J37) that includes the amounts J26:J35 & SUMIF(K21,TRUE,M21).
Could you please help me with this?
SVS1989 As mentioned, you need to get rid of the #VALUE! errors. Then your formula works, although it can be simplified as shown in the pictures I uploaded. See attached.
SVS1989 Why so difficult?
L21 can be =K21*M21
J37 would then be =SUM(J26:J35)+L21
Or, if you want do it all-in-one without using L21:
=SUM(J26:J35)+K21*M21
And obviously, you have to fix the #VALUE! errors in the sum_range. Otherwise the SUM will also return #VALUE!. No matter what formula you put in J37.
Checkbox: TRUE
Checkbox: FALSE
Hi,
Try this instead:
=IF(K21=TRUE,SUM(J26:J35)+125,SUM(J26:J35))
You can change the 125 with your reference to M21 if you wish.
- Thank you very much, Martin.
However, it does not seem to work 😞Does it give you an error? Which one?
Can you try to share a sample file without sensitive data or specify why it does not work?
