How can I sumup column depending on string criterion?

[kebs]

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…

[JohnSUN]

What about =VALUE(MID(A1;2;1)) in column B?

[kebs]

Hey!!! Thanks, I think you found the solution, seems to work. Funny, this means converting a string to a number, I was looking for the opposite… Why not consider posting as answer so I can upvote?

[JohnSUN]

Function VALUE() сonverts a text string into a number. So use

=VALUE(MID(A1;2;1))

[kebs]

Well, can’t even upvote (5pt needed…). Thanks again.