E T wrote:
I have in column A rows of serial numbers-letters. I have in column B rows of
amounts that correspond with the specific serial numbers-letters in the rows of
column A. What formula would I use to search all the column A cells for, for
example, each of the 1234A cells, and total the corresponding amounts?
1234A1 1 9 (total of all 1234A cells)
1234A2 3 6 (total of all 1234B cells)
1234A3 5 8 (total of all 1234C cells)
1234B1 2
1234B2 4
1234C1 8
Thank you.
There are two steps as I see it. The first is to create a column with
the first 5 digits of the serial number; in other words, 1234A instead
of 1234A1.
This can be done with the function "Left". The context is: =LEFT(A3;5)
Where A3 contains the serial number (1234A1), and the formula returns
the left-most 5 digits (1234A).
Once you have the serial numbers converted to 5 digits, then use the
function SUMIF.
The context is
=SUMIF(B$3:B$8;"1234A";C$3:C$8)
where B3:B8 is the range that contains the 5-digit serial numbers, and
C3:C8 contains the amounts.
The following is how I set up the spreadsheet. The formatting will
probably get botched up in the e-mailing process, but hopefully this
provides some idea. The second column, first 6 rows, contain the LEFT
function. The third column, last three rows, contain the SUMIF function.
1234A1 1234A 1
1234A2 1234A 3
1234A3 1234A 5
1234B1 1234B 2
1234B2 1234B 4
1234C1 1234C 8
1234A 9
1234B 6
1234C 8
Hope this helps.
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
