Sumif - wrong cell format

[akuku]

Hello!


I have one question regarding Sumif function.

Please see the image attached - http://postimg.org/image/atnrv2bk3/


Assume we have a column of cells in which are numbers and letters together. I extracted the numbers (col. D) and want to sum numbers applying criteria as in the picture (column C must = 1).

As you can see it does not calculate. What's more basic sum function is not working, however when I add individual cells it works.


Format of column D is 'number'.


Anybody knows how to approach this? It's definitely something wrong with format (?)


Thanks in advance!

 

[SirJB7]

Hi, akuku!


Add a VALUE function to formulas in column D:

=VALUE(RIGHT(LEFT(A1,LEN(A1)-3),LEN(A1)-5))


D1:D3 cell formulas are returning numbers but stored as string (output of LEFT & RIGHT functions).


K1 cell formula worked fine since SUM function interprets string values in C column as numbers.


F11 cell formula will work without any change.


Regards!

 

[akuku]

You guys are amazing! Quick and helpful!

Thanks

 

[Colin Legg]

The

RIGHT() worksheet function is a function which has been included in Excel so people can work with strings. As such, it has been defined to return a string, so the numbers you have in D1:D3 are actually considered by Excel to be string types rather than number types. There's a small giveaway in that the numbers (which are really strings) are aligned to the left of the cell. Normally number types are aligned to the right of the cell.


If you want to add them you need to tell Excel that it should be treating them as number types. One way to do this is to coerce them from text types to number types by performing a basic arithmetic operation on them. For example, you could add zero to them. Your formula in D1 would be:


[code]=RIGHT(LEFT(A1;LEN(A1)-3);LEN(A1)-5)+0


This is exactly the same as your current formula except I have appended [code]+0 to the end of it. Note that I'm not looking to improve the logic or robustness of your formula, just show you how to coerce the output of the RIGHT()

function into a numeric type.


Broken down, this is what we're doing:


="1990" + 0[/code]


-->


=1990[/code]


I hope that makes sense.

 

[Colin Legg]

The other conclusion to make is that SirJB is much faster at typing than I am.

 

[SirJB7]

Hi, akuku!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!


@Colin Legg

Hi!

Just a few nanoseconds of difference

Regards!