Hi Alison,

You need to use a dynamic named range in the SUMIF sum_range argument. Note: it doesn't need to be a 'named range', you can just insert the dynamic named range formula in the sum_range argument, it'll just be cleaner to use a dynamic named range.

Mynda

Hi Mynda

Thanks I've looked at that but I  don't think that will work in my scenario as the spreadsheet (i have no control over the design) has blank lines in between departments but the number of rows per department can be different

I've amended the spreadsheet to be more in line

Any other ideas?

Hi Alison,

I wrote this formula and it returned the correct results for Dept A:

=SUMIF(Sheet1!$B$5:$B$15,'Dept A'!$B$1,OFFSET(Sheet1!$B$5,,MATCH('Dept A'!C$3,Sheet1!$C$4:$E$4,0),500))

The 500 can be replaced with any value that will return a range big enough to include all of the data in the column being summed. You can add a few 1000 rows as a contingency if needed.

Mynda

Thanks Mynda

That works but not quite as I do need to keep the x y z separate per department

I've tried the following

=SUMPRODUCT(--(Sheet1!A5:A15&Sheet1!B5:B15=B4),INDEX(Sheet1!$A$4:$E$15,,MATCH('Dept A'!C3,Sheet1!A4:E4,0)))

but get a #value error

I can't see why this would be - can you ?

I'm confused why your second mock-up showed the values for xyz summarised and now in your third mock-up you're saying you need something completely different.

If all you need to do is extract the values from Sheet 1 into separate sheets for each department then you have two options depending on whether you have Office 365 and dynamic arrays or not:

Dynamic arrays (see sheet DeptA for this formula):

=FILTER(Sheet1!C5:E15,(Sheet1!A5:A15='Dept A'!$A4)*(Sheet1!B5:B15='Dept A'!$B4),0)

If you don't have dynamic arrays, then use a PivotTable to Extract Report Pages. See sheets Pivot All, a, b, and c for the PivotTable solution.

That said, I would use the PivotTable over the dynamic arrays because it's easier to maintain.

Mynda

Thanks.  the 2nd mock up is because I was given more information - frustrating I know.

The pivot table option doesn't work for me as all I can do is enter a formula in this workbook.

I'll take a look at the dymamic array.

Can you see why the sumproduct formula would return a #value?

The SUMPRODUCT(...,INDEX formula is like oil and water. If you evaluate the first and second SUMPRODUCT arguments you'll see why. The INDEX formula is returning the whole table and the logical test isn't for the same size range.

Seeing that you just need to return the individual rows, you can use and INDEX & MATCH array formula:

=INDEX(Sheet1!$C$5:$E$15,MATCH('Dept A'!$B4,Sheet1!$A$5:$A$15&Sheet1!$B$5:$B$15,0),MATCH('Dept A'!C$3,Sheet1!$C$4:$E$4,0))

I'd still use the PivotTable over and above this formula any day.

Mynda