Wikipedia:Reference desk/Archives/Computing/2023 November 26
| Computing desk | ||
|---|---|---|
| < November 25 | << Oct | November | Dec >> | November 27 > |
| Welcome to the Wikipedia Computing Reference Desk Archives |
|---|
| The page you are currently viewing is a transcluded archive page. While you can leave answers for any questions shown below, please ask new questions on one of the current reference desk pages. |
November 26[edit]
Excel Question[edit]
Assume we have two columns 'ID' and 'Score' in an Excel spreadsheet, both entered as numbers. There can be multiple entries of the same ID in the ID column, each with an associated score, so ID 4 might appear 50 times. What is the easiest way in Excel to sum all the scores for a particular ID? 2.100.107.194 (talk) 12:37, 26 November 2023 (UTC)
- There are multiple ways of doing this, but for me the easiest is to select the whole table, click the Data tab and then the Filter icon. You should see small arrows on the column headings. Click the arrow to select the value of ID you want. Then go to the cell below the Score entries (or, more clearly, leave one cell gap between the scores and the selected cell) and go to the Home tab. Click the autosum icon. Done. You can repeat this with different ID values selected. Phil Holmes (talk) 13:13, 26 November 2023 (UTC)
- Thank you. I've got rather a lot of IDs and would ideally like to automate this process. Is it possible for me to generate a distinct list of IDs from the first table, enter this list into a separate second table (so all IDs are present in the second table precisely once) and then do some sort of 'sum if' where for each unique ID in the second table Excel searches the first table for all references to it, sums the corresponding scores, and then places this summed value against the given ID in the second table? 2.100.107.194 (talk) 14:42, 26 November 2023 (UTC)
- That is called a pivot table. Don't try to reinvent a pivot table. Just highlight the data and insert a pivot table. 97.82.165.112 (talk) 20:13, 26 November 2023 (UTC)
- Thank you. I've got rather a lot of IDs and would ideally like to automate this process. Is it possible for me to generate a distinct list of IDs from the first table, enter this list into a separate second table (so all IDs are present in the second table precisely once) and then do some sort of 'sum if' where for each unique ID in the second table Excel searches the first table for all references to it, sums the corresponding scores, and then places this summed value against the given ID in the second table? 2.100.107.194 (talk) 14:42, 26 November 2023 (UTC)
- I would select the table, then go to insert-pivot table. In the pivot table dialog, select scores with the function "sum". Done. It will make a new table that has each ID with the associated total score. 97.82.165.112 (talk) 14:37, 26 November 2023 (UTC)
- There are multiple ways of doing this, but for me the easiest is to select the whole table, click the Data tab and then the Filter icon. You should see small arrows on the column headings. Click the arrow to select the value of ID you want. Then go to the cell below the Score entries (or, more clearly, leave one cell gap between the scores and the selected cell) and go to the Home tab. Click the autosum icon. Done. You can repeat this with different ID values selected. Phil Holmes (talk) 13:13, 26 November 2023 (UTC)
- You can use the SUMIF() function for this. For example, if the IDs are in cells A1 to A100 and the scores in B1 to B100 then the sum of scores with ID=1 would be
=SUMIF($A$1:$A$100,1,$B$1:$B$100)(the second parameter could be replaced by a cell reference or other formula). But the pivot table is probably better for the summary list that you want. AndrewWTaylor (talk) 12:04, 29 November 2023 (UTC)