sumif alphanumeric problems

[Guest]

I can not get sumif to recognize cells containing alphanumberic data. I have
changed the data to text but that still does not work.

 

[Ken Wright]

Post an example of your data and your formula and let us know exactly what
you are expecting the formula to achieve.

 

[Guest]

What do you mean by that? Are you trying to sum textstrings?
If so you need to extract the numbers and then sum them, if there is a logic
to it like

abc123

where you always would have 3 letters followed by numbers then you can use

=SUMPRODUCT(--MID(A1:A20,4,255))

note that if there are blank cells or no numbers it will return an error

Regards,

Peo Sjoblom

 

[Guest]

I am obtaining infomation from one spreadsheet and transferring it to another.
Sheet 1
A B C
3 1234567 ABC123 APPLES
4 1234558 B128TC ORANGES
5 1558799 4589 TOMATOES

Sheet 2
A B C
3 1234567 APPLES *
4 1234558 ORANGES

* =SUMIF(Sheet1!$A$3:$A$5,Sheet2!A3,Sheet1!$B$3:$B$5)

My results will pick up the number 4589 in Sheet1 B5 but not the
alphanumberic in B3 or B4, the results will be listed as "0".

 

[Jason Morin]

Are you sure you're not trying to count? You can't sum
alphanumeric.

=COUNTIF(Sheet1!$A$3:$A$5,Sheet2!A3)

HTH
Jason
Atlanta, Ga