Basically, I'm trying to create a data item subtotal for each unique ID that aggregates another cell. The value will repeat for each unique ID, but as the record count is variable (anywhere from 1,500 to 150,000+ records) I would like a flexible formula. I dumped in the formula that definitely doesn't work.
This is easy and I'm missing it:
Assuming column B is sorted then get the subtotal at the first instance of a uid with this in E2 (filled down),
=if(b2<>b1, sumifs(c:c, b:b, b2), text(,))
To get the subtotal on the last instance,
=if(b2<>b3, sumifs(c:c, b:b, b2), text(,))
For unsorted column B use this in E2 and fill down,
=if(countif(b:b, b2)=countif(b$2:b2, b2), sumifs(c:c, b:b, b2), text(,))
You can solve this simply using =SUMIFS() and locked cell references ($):
=SUMIFS($C$2:C2,$B$2:B2,B2)
Put the above code in C2 and copy down, it will return:
1111 3.18 3.18
1111 17.18 20.36
1111 1.38 21.74
1111 1.04 22.78
2222 2.24 2.24
2222 2.24 4.48
2222 2.24 6.72
2222 2.24 8.96
3333 4.39 4.39
3333 4.39 8.78
3333 4.39 13.17
3333 1.66 14.83
3333 -0.66 14.17
3333 -0.66 13.51
4444 3.67 3.67
4444 2.28 5.95
4444 2.33 8.28
4444 2.02 10.3
4444 1.52 11.82
