Quote Originally Posted by Ochimus View Post
Thanks to Jason for clarifying that the formula doesn’t work with Quarterly or Annual data because it doesn’t look for “text”. Hopefully someone out there knows what to add already?
Sunday morning caffeine deficiency took effect earlier, I forgot to include the formula in my last post.

Enter this formula in N2 of your V2 sample.

=SUMPRODUCT((INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!A2:A23")=$A2)*(INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B1:BB1")=IFERROR(--LEFT(N$1,FIND(" ",N$1)-1),LEFT(N$1,FIND(" ",N$1)-1)))*INDIRECT(MID(N$1,FIND(" ",N$1)+1,100)&"!B2:BB23"))

Regarding efficiency, the problem would arise more with a large number of rows in the comparison sheet than with the volume of data in the other sheets.

Indirect is one of the 'volatile' functions, as such any formula that uses it is recalculated when any action causes recalculation, even if there is no reason for that specific formula to be recalculated.

See http://www.decisionmodels.com/calcsecretsi.htm