excel - Subtract two cells based on multiple criteria

Question

I'm trying to calculate a daily count, given that I have current date, city, and total count.

For each row, I imagine [city's current total] - [the previous total for that city] = the daily count.

Not all cities exist in every date. The number of rows may vary between dates for each city.

I've tried INDEX and MATCH with VLOOKUP, but don't Excel functions well enough.

I'm familiar with arrays, but can't figure how to pluck the UBOUND and UBOUND-1 counts based on the date + city criteria. Ideas?

标签： excelcalculated-field

Answer 1

在 E2204 中输入：

=D2204-INDEX(D:D,AGGREGATE(14,7,ROW($D$1:$D2203)/($C$1:$C2203=C2204),1))

或者，如果有公式 XLOOKUP：

=D2204 - XLOOKUP(C2204,$C$1:$C2203,$D$1:$D2203,0,0,-1)