Forum Discussion
Aggregate by Two Columns Sum a Third
New to the community so hello. I've searched and searched for a solution with no success. What I need to do is group rows of data by two separate columns and then sum the values in a third related column. I want to place the results in three separate columns on a different tab. An example is attached although the desired results are on the same tab. Can this be done with a formula or will this require a macro? Any suggestions would be appreciated.
Inpuit |
|
|
| Sum of each group |
|
| Group by | |
Payto Prov ID | Payto Prov Type Number | Recoupment Percentage | Paid Date | Overpayment | Fund Code | Code Type | Upload Fund Code | Federal Year / Calendar Year |
1 | 26 | 0.01 | 9/30/2015 | 90.00 | 1102ACA | AC | 4715ACA | 2015 |
1 | 55 | 0.01 | 6/30/2015 | 25.00 | 4502ACA | AC | 4715ACA | 2015 |
1 | 25 | 0.01 | 10/1/2016 | 100.00 | 1102ACA | AC | 4715ACA | 2017 |
1 | 72 | 0.01 | 10/2/2016 | 150.00 | 1103ACA | AC | 4715ACA | 2017 |
1 | 5 | 1.00 | 1/1/2017 | 500.00 | 1103ACA | AC | 4715ACA | 2017 |
1 | 65 | 0.01 | 10/1/2016 | 200.00 | 1103SFO | SF | 4715REG | 2017 |
1 | 38 | 0.01 | 11/25/2016 | 10.00 | 4552SCH | SC | 4715SCH | 2017 |
1 | 9 | 1.00 | 1/1/2018 | 75.00 | 4549SCH | SC | 4715SCH | 2018 |
Desired Results
$115.00 | 4715ACA | 2015 |
$750.00 | 4715ACA | 2017 |
$200.00 | 4715REG | 2017 |
$10.00 | 4715SCH | 2017 |
$75.00 | 4715SCH | 2018 |
Thanks
Jeff
2 Replies
Jeff,
the easy way: Insert a pivot table. "Upload Fund Code" and "Federal Year / Calendar Year" in row area and "Overpayment" in values area.
I prefer easy. I'll insert a pivot table. I was hoping that it could be done with a formula but I'm starting to think it's not possible or worth the effort.
Thanks for the response.
Jeff
