I am struggling to get a solution to work, any advice would be gratefully received.

In 'Sheet 1' I have:

Name % Share
AB 10
CD 20
EF 30
GH 20 etc. etc.


In 'Sheet 2' I have:

Quarter Name Base sum
1
2
3

I am entering multiple Quarters per Name and Base sums.

i.e. I could have AB appearing 5 times with an entry for quarter 1 and 6 times with an entry for Quarter 2.


In 'Sheet 3' I have:

Quarter Total Base Total % Share
1
2
3

'Sheet 3' is a summary sheet, so all entries from Sheet 2 compact down into single row quarters.


I am using the following:

=SUMIF('Sheet 2'!A:A,'Sheet 3'!C2,'Sheet 2'!C:C)

To give me a total base cost against each quarter. I am copying that formula down to each quarter.

I want to calculate in 'Sheet 3' for each quarter 'Total % Share' which would be the total base for each 'Name', multiplied by their respective '% Share' in 'Sheet 1'.

I know I can do this by adding a column to 'Sheet 2', calculating that via a lookup and then summing that in a similar way to the base sum.

However for various reasons, I don't want that info to appear on sheet 2 and would like to avoid a separate 'data table' if possible. I have tried various array formulas but I am only ending up with a 0 result which is clearly wrong.

Can anyone help and I hope the above is clear.

Thanks!