Richard Detwiler wrote:
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]
You can also do it in one step using Sumif and regular expression syntax
as follows:
=SUMIF(A5:A10;"1234a.*";B5:B10)
=SUMIF(A5:A10;"1234b.*";B5:B10)
=SUMIF(A5:A10;"1234c.*";B5:B10)
This allows the use of wildcards in the criteria filter.
TomW
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
