COUNTIF

[Connie]

Working with Excel 2000. I have several columns where I
need take the sum a variety of units of measure (UOM),
which is a mix in each column, BUT the unit of measure is
preceded by a number (within the same cell) which could be
any number. So, for example, in a column I could have
something like the following:
3,199 CAR (this would be in one cell)
1,113 CAR (same thing...in one cell)
1,075 MU (in the same cell)
22.9 CAD / CAR (in the same cell)
1.20 CAD / CAR (in the same cell)
975 MU

So, you see I have, in this example, two different types
of unit of measure (CAR and MU) in this column of
numbers. This is a huge file downloaded from Business
Warehouse and I cannot change any of the formatting. It
must remain as it is. What formula would I use (if
possible) to sum up all the numbers where the UOM
is "CAR", for example? In the example above, the total of
that column for CAR would be 4,336.1. I have 547 rows of
numbers in a very complex worksheet. I will also be
summing the UOM, MU, as well.

 

[Naraine]

you need to extract the numbers from the letters.

assuming the are in column A

TO Extract the letters, type the following in blank column

=MID(A1,FIND(" ",A1,1)+1,LEN(A1))

to extract the numbers, type the following in the next blank column

=MID(A1,1,FIND(" ",A1,1))*1

to compute sumif for "car"

=SUMIF(D16,"car",E1:E6)

assuming d1:d6 contains the extracted letters and e1:e6 contains the values.

hope this helps.

 

[Jason Morin]

To sum for CAR, try:

=SUMPRODUCT((ISNUMBER(FIND("CAR",A1:A547)))*(LEFT(TRIM
(A1:A547),FIND(" ",TRIM(A1:A547))-1)))

HTH
Jason
Atlanta, GA

 

[Connie]

Thank you for responding. Things have got even more
complicated than I realized. What I didn't realize is the
the UOM's are not typed in the cells. They are there but
not typed in. Sounds bizarre, but when you click on the
cell all that shows up in the formula bar is the number.
This has been downloaded from Business Warehouse and I
think really it needs to be configured further there
before downloading to Excel. Thank you for taking the
time. I will print your posts, though, because I may run
into this again in a simpler fashion! Connie

 

[Naraine]

i beleive what you have is the cell formatted using custom format.

click the right mouse|format cells...

 

[ivano]

Working with Excel 2000. I have several columns where I
need take the sum a variety of units of measure (UOM),
which is a mix in each column, BUT the unit of measure is
preceded by a number (within the same cell) which could be
any number. So, for example, in a column I could have
something like the following:
3,199 CAR (this would be in one cell)
1,113 CAR (same thing...in one cell)
1,075 MU (in the same cell)
22.9 CAD / CAR (in the same cell)
1.20 CAD / CAR (in the same cell)
975 MU

So, you see I have, in this example, two different types
of unit of measure (CAR and MU) in this column of
numbers. This is a huge file downloaded from Business
Warehouse and I cannot change any of the formatting. It
must remain as it is. What formula would I use (if
possible) to sum up all the numbers where the UOM
is "CAR", for example? In the example above, the total of
that column for CAR would be 4,336.1. I have 547 rows of
numbers in a very complex worksheet. I will also be
summing the UOM, MU, as well.

I don't undestrand so well...I'm italian.
I think:
=COUNTIF(B9:B12,"*"&"car"&"*")
..i