sumif alphanumeric problems
 

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

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

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

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

"buyer1" wrote:

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

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".

"Ken Wright" wrote:

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

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

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

-----Original Message-----
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".

"Ken Wright" wrote:

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

--
Regards
Ken....................... Microsoft
MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

-------------------------------------------------------
---------------------
It's easier to beg forgiveness than
ask permission :-)
-------------------------------------------------------
---------------------

"buyer1" wrote in
message
news:F53085E3-7350-4610-B149-
...
I can not get sumif to recognize cells containing
alphanumberic data. I
have
changed the data to text but that still does not
work.



.