I have a Table in Excel 365 called MyLongData that contains multiple rows. I would like to come up with a formula that tracks claims, where a claim is defined as a unique combination of fields [Accident Date], [Claimant Name], [Location].
I want to add up the [Amount] field for claims where [Amount]<=10000,
furthermore, I want to restrict it to where field [Year] = 2023.
So to summarize, I would like to add up [Amount] given [Amount]<=10000 grouped by combination of [Accident Date], [Claimant Name], [Location] in a table MyLongData.
Simple example:
| Accident Date | Claimant Name | Location | Year | Amount |
|---|---|---|---|---|
| 1/1/2020 | Jake | Denver | 2021 | 20000 |
| 2/1/2023 | Jill | Seattle | 2023 | 9950 |
| 2/1/2023 | Jill | Seattle | 2023 | 100 |
| 5/6/2023 | Blake | Miami | 2023 | 5000 |
I want to return 10050 (the sum of records 2 and 3, which are a single claim that exceeds the 10000 threshold).
I managed to figure out the formula for the COUNTS, which I believe is correct:
=IFERROR(ROWS(UNIQUE(FILTER(
MyLongData[Accident Date]&MyLongData[Location]&MyLongData[Claimant Name],
(MyLongData[Year]=2023)*(SUMIFS(MyLongData[Amount],
MyLongData[Accident Date],MyLongData[Accident Date],
MyLongData[Location],MyLongData[Location],
MyLongData[Claimant Name],
MyLongData[Claimant Name])<=10000)))),0)
But I can’t get the formula to add up the amounts.
I’d prefer a single (however complex) formula, but I can also resort to a Pivot Table approach, if that’s the only way.