SUM of cells that have conditional formatting applied to them

[Ryancc49]

Hi, sorry I’m not too advance with excel so may be an obvious answer to my problem!

I’ve got a budget spreadsheet that has cells with IFS conditions based on how much someone is charged for an event based on their job banding (band 3 pay £45, band 5 pay £50 eg =IFS(B4=3, “45”, B4=5, “50”). So now have a column of how much is due from each person.

Is there a way to add the value of the cells in that column that have this formatting applied to them? Doing a simple =SUM(B4:B99) will give a value of 0 as the cells are conditionally formatted and don’t actually hold the value of 45 or 50 but the IFS condition

I hope this makes sense! Thanks in advance!

 

[Joe4]

Welcome to the Board!

That is not Conditional Formatting, but rather a conditional formula.

The reason why SUM is not working is because you are returning text values, not numeric ones.
Change your formula to this:

=IFS(B4=3,45,B4=5,50)

and your SUM should work.

This is because anything enclosed in double-quotes is treated as literal text.
Numeric values should NOT be enclosed between double-quotes if you want to do mathematical operations on them.