Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.
Trending News
How do you combine this cells in Excel?
OK I have similar data to this separated in columns & rows as follow.:
Type / Serial / Price: / Amount / Total
1. ahh / 123456 / $10.00 / 2 / $20.00
2. ahh / 123456 / $20.00 / 1 / $20.00
3. ahh / 123456 / $10.00 / 1 / $10.00
4. ahh / 44444 / $10.00 / 1 / $10.00
5. ahy / 44654 $10.00 / 1 / $10.00
and so forth...
I need to know if there is an automatic way (without tables) to make excel by filtering or other options, take all serial numbers that are the same & add them up automatically. For instance: to have a column that has all the totals of the serial numbers 123456 without having to go into each manually... Thanks!
Sorry I forgot hhow to include this:
- I have a listing of about 1000. I need to know if there is a way of automerging all repeated Serials. So if there is a serial repeated twice, for excel to go into it and auto sum it automatically. So that I don't have to go into every single one & do it manually... Thank you so far..
5 Answers
- garbo7441Lv 71 decade agoFavorite Answer
Assuming your columns are A-E, with serial number in B and 'total' in E, then put this formula in F1:
=SUMIF(B:B,"="&B1,E:E)
Drag the formula down column F through the last row.
The total for each serial number will appear in column F. Bear in mind, it will show the 'total' for each instance of each serial number. So, if '123456' appears 10 times, all 10 entries will show the total in column F.
- 1 decade ago
First sort the column with the serial number in either ascending or descending order. Assuming the column with the serial numbers is column B, do the following:
1. In cell G2, put the formula "=B2"
2. In cell G3, put the formula "=OFFSET($B$1,MATCH(G2,B:B,0) +COUNTIF($B:$B,G2)-1,0)" and drag down. This formula will pick up each serial number in column B one time.
3. In cell H2, put the fomula "=SUMIF($B:$B,G2,$E:$E)" to sum all the totals of each serial number.
After you get formulas in columns G and H to work, I would cut the formulas and place them in a new tab so that you don't have to worry about the formulas getting messed up because they're on the same page as the data.
The formula from number 2 above is give an error reference if all serial numbers are accounted for so you can actually do the following so that you don't see the error values:
=if(iserror(OFFSET($B$1, MATCH(G2,B:B,0) +COUNTIF($B:$B,G2)-1,0)),"",OFFSET($B$1, MATCH(G2,B:B,0) +COUNTIF($B:$B,G2)-1,0))
- ?Lv 44 years ago
to make sparkling the previous posts. The " " is a text textile string with an area character in between the charges. This areas the words properly. an decision could be to incorporate an area after the words in each cellular, then the formula could be: =concatenate(A1,B1,C1)
- 1 decade ago
there is an autosum button at the top right of 2007 excel, if you have an older version type in autosum in the help search bar
it looks like this http://www.csuchico.edu/stcp/workshops/excel_2007_...
- How do you think about the answers? You can sign in to vote the answer.
