Dynamic Sumifs Formula

**skycowboy** · 03-09-2011, 11:12 AM

Hi,

Please see attached spreadsheet.

I have a table with same layout (number of columns / position of columns) on each worksheet and each worksheet represents a different quarter.

I'd like to extract comparative data for each quarter to different cells per Sheet 7 based on a set of predefined criteria.

Currently I am able to use the Sumifs formulae but I have to keep changing the formula to look at different worksheet names.

I'd like the Sumifs formula to know that inter alia in:
* Cell B4 I want to extract the data from worksheet Q1 2009.
* Cell C4 I want to extract the data from worksheet Q2 2009.
* Cell B5 I want to extract the data from worksheet Q1 2010.

Perhaps Sumifs formula is not the best answer.

I hope that I have explained myself correctly and that the attached worksheet helps to explain what I'm looking for.

Many thanks,
Rob

**skycowboy** · 03-11-2011, 12:11 PM

Hi All,
Does anyone have a solution?
Many thanks,
Rob

**daddylonglegs** · 03-11-2011, 12:47 PM

Hello Rob, try this formula in B4 copied across and down

=SUMIFS(INDIRECT("'Q"&MOD(B$3-1,4)+1&" "&$A4+INT((B$3-1)/4)&"'!E:E"),INDIRECT("'Q"&MOD(B$3-1,4)+1&" "&$A4+INT((B$3-1)/4)&"'!D:D"),$A4,INDIRECT("'Q"&MOD(B$3-1,4)+1&" "&$A4+INT((B$3-1)/4)&"'!C:C"),$A$2,INDIRECT("'Q"&MOD(B$3-1,4)+1&" "&$A4+INT((B$3-1)/4)&"'!F:F"),B$2)