I am trying to write a formula that would sum up the total amounts based on the invoice id changing (see table below). I additionally, have a quantity that needs to be multiplied by the amount and then sum the total. The Invoice ID does not copy down so it needs to change when it finds the next Invoice ID to calculate again.
*Invoice ID | Line Number | *Line Type | *Amount | Invoice Quantity |
1001 | 1 | ITEM | 500.00 | 2 |
2 | ITEM | 300.00 | 3 | |
1002 | 1 | ITEM | 250.00 | 4 |
2 | ITEM | 300.00 | 5 | |
3 | ITEM | 100.00 | 6 | |
1003 | 1 | ITEM | 100.00 | 4 |
2 | ITEM | 200.00 | 1 | |
3 | ITEM | 300.00 | 2 | |
4 | ITEM | 400.00 | 6 | |
1004 | 1 | ITEM | 100.00 | 2 |
2 | ITEM | 200.00 | 1 | |
3 | ITEM | 300.00 | 6 | |
4 | ITEM | 400.00 | 4 | |
5 | ITEM | 500.00 | 2 | |
1005 | 1 | ITEM | 1000.00 | 1 |
1006 | 1 | ITEM | 100.00 | 3 |
2 | ITEM | 100.00 | 5 | |
1007 | 1 | ITEM | 200.00 | 4 |
2 | ITEM | 300.00 | 3 | |
3 | ITEM | 400.00 | 2 |