Indirect and substitute formua with sumifs

Hello,

I'm wondering if anyone can help me with a formula that will allow me to use information from 3 data validation lists to calculate the sum of values between 2 dates in a specific worksheet and column.

I have attached a copy of my test spreadsheet as it is difficult to explain. The formula I'm having trouble with is in the total cell 'E14'.

My formula so far is:

=(SUMIFS(INDIRECT("'"&$E$4&"'!$B:$B"),INDIRECT("'"&$E$4&"'!$A:$A"),">="&$H$7,INDIRECT("'"&$E$4&"'!$A:$A"),"<="&$H$10))

Basically, the part I need help with is replacing !$B:B with a named range that will return data from the correct worksheet and column.

Thanks in advance for your help,

Emily

Marvin - Thanks for your suggestion. However I'm not sure that the pivot option will work as the spreadsheet will be used by people with very limited computer skills. The yearly lists will also contain a heap more data (water consumption recorded at 5 minute intervals for the entire year for each site. Additionally, once I get the basic formula sorted I'll also be extrapolating it out to display average, min and max values from the range selected. I'll explore the pivot a little more just in case I can make it usable for them.

Tony - Thanks also for your help. I haven't used any functions within the named range (that I'm aware of). All I did was highlight the cells and apply a name to it.

Hi Tony,

Yes, the named range is on the sheet referenced in cell E4.

Just with your second note - does this mean named ranges in the worksheet I'm trying to get the formula to read from or include all lists I created for the drop-downs too?

Thanks,

Emily

Hi Tony,

I've attached another file. I'm not entirely sure why it is so big as I've only got 4 worksheets with about 4 rows of data in each.

Thanks,
Emily

Hi Tony,

Thanks so much for your persistence - your solution is perfect!

Sorry about the extra data I missed. Nice pick up on your part.

Thanks again,

Emily