Sorry for the messy presentation here but I wanted to show the formulas as well. Trying to calculate total revenue (using sumproduct) based on various price sets for different products. Need this to stay dynamic and also account for filtered/hidden data. INDEX and XLOOKUP bring in the contents of the variable column but only work with AGGREGATE when rows are hidden. I like offset because the relative reference changes as copied over but will only spill when nested in SUBTOTAL (for some reason it does not sum like INDEX and XLK). This one doesn't work with AGGREGATE. I've reached my time limit on this. Any words of advice or added wisdom?
Quantity A | Price A | OFFSET | INDEX | XLOOKUP | |
1 | 10 | #VALUE! | 10 | 10 | |
2 | 20 | #VALUE! | 20 | 20 | |
4 | 40 | #VALUE! | 40 | 40 | |
5 | 50 | #VALUE! | 50 | 50 | |
=OFFSET(C27,SEQUENCE(COUNT(C27:C31),1,0),,) | =INDEX(A27:C31,,XMATCH(C26,A26:C26)) | =XLOOKUP(C26,A26:C26,A27:C31) | |||
=AGGREGATE(9,5,OFFSET(C27,SEQUENCE(COUNT(C27:C31),1,0),,)) | #VALUE! | | 10 | 150 | 150 |
| | | 20 | 120 | 120 |
30 | =SUBTOTAL(9,INDEX(A27:C31,,XMATCH(C26,A26:C26))) | =SUBTOTAL(9,XLOOKUP(C26,A26:C26,A27:C31)) | |||
40 | =AGGREGATE(9,5,INDEX(A27:C31,,XMATCH(C26,A26:C26))) | =AGGREGATE(9,5,XLOOKUP(C26,A26:C26,A27:C31)) | |||
50 | |||||
=SUBTOTAL(9,OFFSET(C27,SEQUENCE(COUNT(C27:C31),1,0),,)) |
