I have a problem with the SUMIF()
function, that I think is related to the way LO (4 and 5) handles values either as strings or numerical values.
Consider a blank sheet in wich I enter this:
A B C D
1 A1A =MID(A1;2;1) 1 =IF(B1=C1)
Then the cell B1 will appear as 1
and D1 will have the value FALSE
, because for string comparison, we need to use the EXACT() function: the correct formulae would be =EXACT(B1;C1)
But know say I have this:
A B C
1 A1A =MID(A1;2;1) 10
2 A2B =MID(A2;2;1) 11
3 A1C =MID(A3;2;1) 12
And I want to sum up the C-column values depending on what I have in column B:
A B
5 1 =SUMIF(B1:B3;A5;C1:C3)
6 2 =SUMIF(B1:B3;A6;C1:C3)
Well… this doesn’t work, I get 0. And I don’t seem to be able to use the EXACT()
function here. I have tried setting the A5
cell format as “text” but it doesn"t do anything.
How can I solve this issue ? This can’t be a bug, I’m probably missing something…