Hi everyone,
First time posting so excuse the messiness (not sure if the screen shots will show up either)
I am trying to summarise data from multiple tabs however the tables I am referencing vary in size (vertically) across each of the tabs.
I was hoping I could use SUMPRODUCT combined with INDIRECT to achieve this on my summary tab
First I have made sure my SUMPRODUCT works on its own
=SUMPRODUCT('Property 1'!AM7:AP172,('Property 1'!AM7:AP7=C1)*('Property 1'!AM7:AM172=B2))
excel sumproduct1.PNG
Then I have added a column (A) to include the name of the tab I want to lookup using my INDIRECT addition to the SUMPRODUCT
=SUMPRODUCT((INDIRECT("'"&$A2&"'!AM7:AP224")),(INDIRECT("'"&$A2&"'!AM7:AM224"=B2)*(INDIRECT("'"&$A2&"'!AM7:AP7"=C1))))
Here are the parameters
1. Tab name (cell A2 in summary tab) - Property 1
2. Array (cells AM7:AP224 under the Tab called Property 1 (A2 in summary tab))
3. Criteria 1 range (cells AM7:AM224 under the Tab called Property 1 (A2 in summary tab)) – the month
4. Criteria 1 (cell B18) – the month
5. Criteria 2 range (cells AM7:AP7 under the Tab called Property 1 (A2 in summary tab)) – the category called ‘purchase’
6. Criteria 2 (cell B18) – the category called ‘purchase’
excel sumproduct2.PNG
However its returning a #REF!
Here is the array1 from 'Property 1' tab
excel sumproduct.PNG
Any help would be much appreciated, cheers.
Bookmarks