CODEWITHSUNDEEP
excelexcel-formula
D Nels
D Nels

Reputation: 1

SUMIF INDEX MATCH, with a variable Index

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:
This is easy and I'm missing it

Upvotes: 0

Views: 132

Answers (2)

girlvsdata
girlvsdata

Reputation: 1644

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

Upvotes: 0

user4039065
user4039065

Reputation:

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(,))

Upvotes: 2

Related Questions