Excel SUM formula being weird.

[Rob_D]

Hey,

 

I'm trying to use a SUM formula to add to cells together (one is just a number and the other is the numerical result of a formula).

So I'm using =SUM(D2: D3), to add 28 and "=IF('2023-24'!D5<3,'2023-24'!D5,"3")" (which in this case outputs as 3), and it gives me "28" as the output.

But if I use =D2+D3 then it works fine and gives me "31".

 

Anyone know why?

[andy_b]

The quotes around the 3 are making Excel think it isn't a number.

 

Not sure tbh why + ignores that.

[ABR82]

by putting the 3 in quotations, you're making it text, rather than a number, so it isn't included in the sum. Put just 3 for your value if false (no quotation marks). You can see this in how it formats it - left aligned in the cell for words, rather than right for numbers).

 

Quite why adding them rather than using the SUM function allows it to recognise it as numeric is a different question! but the sum and average functions ignore cells with text in and return the overall result, whereas if you put 3 in cell A1 and write TEN in A2, then using =A1+A2 it will give you an error, whereas SUM(A1:A2) will return 3.

 

Not sure there's an answer there as to why, but perhaps knowing how the different functions behave helps?

Edited January 29, 2024 by ABR82

[6Foot2]

You could try:

 

=IF('2023-24'!D5<3,'2023-24'!D5,VALUE("3"))

 

or

 

=IF('2023-24'!D5<3,'2023-24'!D5,NUMBERVALUE("3"))

 

This keeps the quote marks around "3" should you want to do that.

 

*I haven't been able to test this because I don't have Access to Excel.