Forum Discussion
Find matching values in column cells and the sum of of the cells in the rows of each matching cell
I am at a complete loss on how to do this and I am hoping for some help. Probably easiest to demonstrate with two images below with a small set of hypothetical data. In the first image, you can see column A has duplicated values. The formula I hope to build is one that will find all values in column A that match and then add up all the values in Column B that correspond to the matching cells in Column A. For example, there are two cells in Column A that have a value of Jim McDaniel and values in Column B are 30 and 4 (sum of 34 for Jim McDaniel).
On a summary worksheet, I would like the results of the first worksheet to be displayed as per the example below. I'd like one row for each of the matching values from Column A from the first worksheet and then Column B would show the sum of the values from the corresponding values from Column B in the first worksheet.
Any and all help is MUCH APPRECIATED!!
Hi Bill,
The easiest way to do this is by using the PivotTable as the below screenshot:
To learn more about PivotTables, please check out this https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576 and https://www.excel-easy.com/data-analysis/pivot-tables.html.
Hope that helps
7 Replies
Hi Bill,
The easiest way to do this is by using the PivotTable as the below screenshot:
To learn more about PivotTables, please check out this https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576 and https://www.excel-easy.com/data-analysis/pivot-tables.html.
Hope that helps
Haytham Amairah I am attempting to do something very similar but I am trying to match multiple columns/rows of data and bring back a sum. Example below: if A2-E2 match exactly A3-E3 then total the sum of those matching rows, which should come back as zero (8.2-8.2 or F2+F3). I put this all in a pivot but need the zeros to not show. I only need the unique row's sum, and the comment needs to show for each one. I appreciate any ideas. I have tried a calculated field in a pivot but it wont pull the comments.
Clock Last First Event Date Sum Comment 358 Smith Joe no show 2/10/2021 8.2 Test 358 Smith Joe no show 2/10/2021 -8.2 Test 358 Smith Joe no report 2/11/2021 8.2 Test 456 Smith Ted no show 02/01/2021 8.2 Test 456 Smith Ted no report 02/16/2021 -8.2 Test 456 Smith Ted no report 02/16/2021 8.2 Test 456 Smith Ted FMLA 02/15/2021 8 Test This is SO helpful! Thank you! Here is a followup question. Do you know if there is a way to automatically delete from the first sheet the rows that have an aggregate value from Column B in the summary that have a value below a certain number? For example, if the threshold is "300" would it be possible to automatically delete the source data for Jim and Joe because their aggregated column B data is less than 300. I hope that makes sense.
Haytham Amairah wrote:Hi Bill,
The easiest way to do this is by using the PivotTable as the below screenshot:
To learn more about PivotTables, please check out this https://support.office.com/en-us/article/Create-a-PivotTable-to-analyze-worksheet-data-A9A84538-BFE9-40A9-A8E9-F99134456576 and https://www.excel-easy.com/data-analysis/pivot-tables.html.
Hope that helps
Hi Bill,
You can easily filter out the aggregations that don't meet certain criteria as the below screenshot:
Regards,
Haytham
