excel - SumProduct, doesn't return me text and number. (Only Number) -

at first answer)) (it's important me :p )

i have number in a3.

when there number in column (sheet1), per exemple a7 take value of cell b7.

 =sommeprod(('sheet1'!a3:a34=sheet1!a3)*('sheet1'!b3:b34)) 

i use sommeprod, it's working number, , have text , number in cell (column b).

i changed format of cell doesn't work.

thanks lot)))

i think saying of values in column b expressed text, not number, because imported source.

if case: first, convert column b numbers, then, use sumproduct.

for example, first convert text values: in c3 enter

=value(b3) 

then copy down c3 c34.

then

=sommeprod(('sheet1'!a3:a34=sheet1!a3)*('sheet1'!c3:c34)) 

should have desired effect.

note sometime #n/a errors "value" function, example if there unexpected spaces. safe, rather using value alone, try this:

= iferror(value(b3),0) 

or more creative can try:

= iferror(value(substitute(b3," ","")),0)  ' deletes spaces = iferror(value(substitute(substitute(b3,char(160),"")," ","")),0)   'deletes spaces , "nonbreaking spaces" copied web page