Harlan Grove wrote:
Person wrote...
...
Thanks so much - this is awesome - if I may, one quick question - if
the data that makes up TRANS (the transactions) is located on more
than one worksheet, will this solution still work? Can I name a
range TRANS that is composed of data that lives in different
worksheets? Hopefully I have not misunderstood your reply.

No, you can't make TRANS span multiple worksheets. If you have, say, 4
such worksheets, you'd need to name each of them, e.g., TRANS1, TRANS2,
. . ., TRANS4 and change my formulas to something like

B2 [array formula]:

=SUM((INDEX(TRANS1,0,1)=TRANSPOSE(IF(INDEX(MRSR,0, 1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS1,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$ A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS2,0,1)=TRANSPOSE(IF(INDEX(MRSR,0, 1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS2,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$ A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS3,0,1)=TRANSPOSE(IF(INDEX(MRSR,0, 1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS3,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$ A2,INDEX(MRSR,0,3)))))
+SUM((INDEX(TRANS4,0,1)=TRANSPOSE(IF(INDEX(MRSR,0, 1)=$A2,INDEX(MRSR,0,2))))
*(INDEX(TRANS4,0,2)=TRANSPOSE(IF(INDEX(MRSR,0,1)=$ A2,INDEX(MRSR,0,3)))))

There's no simple way around this. Excel is horrible at this sort of
consolidation. Other spreadsheets would be nearly as bad. (Yes, nearly
rather than more so - Excel is in a class by itself for POOR 3D
capabilities.) Databases make much, much more sense for this sort of
categorical counting/summing.

Wowser. I will give it a shot! I am very grateful for the assistance.