SUMIF with numbers stored as text

[calebtemple]

Hi Guys,

I have a spreadsheet I want to use a SUMIF on, but column I want to sum is stored as text. I don't want to have to convert the column to text, I wanted to do it using a formula, but can't quite get the array formula right and hoping for some help

Sheet one is something like the following
[TABLE="width: 500"]
<tbody>[TR]
[TD]Column A[/TD]
[TD]Column B[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]200[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]300[/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD]400[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]200[/TD]
[/TR]
</tbody>[/TABLE]
NOTE: Column b are numbers stored as text

Now if Column B above was numbers, it's simple,
[TABLE="width: 500"]
<tbody>[TR]
[TD]Col F Names[/TD]
[TD]Col G Totals[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]=sumif(A:A,F1,B:B)[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Sue[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

But what do I do if those numbers in column B are formatted as text?

 

[Useful]

Hello!
try
=SUMPRODUCT((A2:A6=F1)*(B2:B6))
to convert B column numbers to value - copy blank cell and select range Column B and right click paste special PAste- value, operation - add and ok.

 

[calebtemple]

Hi - no didn't work, gave me a #VALUE! result, but I didn't convert the Text columns to numbers - I'm hoping for a formula to convert it on the fly, I was hoping I could use the VALUE formula, but it failed

 

[vlady]

try this array

=SUM(IF((A2:A6=F1)*(ISNUMBER(VALUE(B2:B6))),VALUE(B2:B6)))

 

[yesterdays]

The basic formula =sumif(A:A,F1,B:B) still working for me, even if column B formatted as text, have you tried it ?

 

[yesterdays]

Ok my bad, it didn't work ,
try this :
=SUMPRODUCT((A2:A6=F1)*1,B2:B6*1)

 

[Useful]

Hello!
My suggested post works perfectly even your range (column B) contains values that formatted as text. Try again
Hope this helps!