Cannot get INDIRECT function to work with SUMIFS

Instead of trying to transpose the totals, you can work them out directly by putting this formula in I2:

=SUMIF(INDIRECT("'"&$H2&"'!A:A"),I$1,INDIRECT("'"&$H2&"'!D:D"))

Then you can copy this across to Q2, and then copy I2:Q2 down (when you have Sheet 2 and the others in place).

Hope this helps.

Pete

Hey Pete, that's great - thanks! It works brilliantly. I now notice by the way that my original description was wrong - I'm trying to get SUMIFS to work with INDIRECT, not SUMIF - I'll correct this now. So, the second part of my problem is resolved but not the first. I applied the syntax you suggested to my SUMIFS formula but still get an error message.

Okay, see the attached file. I've copied Sheet 1 and called it Sheet 2, and the formulae in the sub-table for Sheet 1 can be copied to the other sub-tables, as long as you have sheets with the names in the top corner of the sub-table. See my notes in blue.

Hope this helps.

Pete

Pete; just fantastic! You're are an absolute star! Many thanks!!

Thanks for feeding back. The "trick" in this is that I set up the formulae in the top line of the table for Sheet 1 using the reference $A$2, so when I copied the formulae down to the bottom of the table the references did not change. I then highlighted the 4 columns of formulae and did Find&Replace to change $A$2 to A2, so that all cells were now referenced relatively to the sheet name in A2. This then allows you to copy the whole block down into a new area, and the formulae will reference whatever sheet name is in that upper left corner of the table.

Pete

That's a really neat trick Pete - thanks again.

Kevin.