Forum Discussion
Find matching values in column cells and the sum of of the cells in the rows of each matching cell
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,
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
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
That is also helpful but I would ideally like to delete the source data. This is because in the end I need to go back to the source data and export 1 complete record (all fields from the source data) from each record shown on the filtered summary. Ideas?
You can't delete the records in the source table that its aggregation is less than a certain threshold unless you use a Macro/VBA solution which it could be difficult!
But I have a fairly good and easy workaround for this case.
Before you create the PivotTable, please create another column in the source table that contains this formula:
=IF(SUMIF($A$2:$A$9,A2,$B$2:$B$9)>300,TRUE,FALSE)
Then filter out the FALSEs and copy the filtered table into a new worksheet as below:
After that, create a new PivotTable from the new source table as below:
Regards