I have a very large spreadsheet with muliple named tabs and multiple named ranges within each tab. I will try to be specific but not long winded... The quick explaination is that I am on a construction project that we have to pay an hourly rate to close lanes. The amount we pay per hour depends on time of day, day of the week, number of lanes closed, and section of the road closed. There is a different tab for each section of road. Within each tab are named ranges (one per column in columns B-Z) that uniquely list the fee amounts by day of the week and number of lanes closed. Time is hourly and is in column A on each tab. I want the user to put in basic information have the spreadsheet do the rest of the work. Once the user indicates all variables the spreadsheet should do a sumifs where it sums a certain range (the start time and end time) in the correct named range on the correct tab. The summary tab has the section of road in column U, the named range in column X, the start time in column P and the end time in column R. I think I need to have something along the lines of

=sumifs(indirect("X6"),indirect("U6")&!A4:A99,"=" &P6,indirect("U6")&!A4:A99,"<="&R6)

It appears the error is in the criteria range. I hope this all makes sense... any ideas?

I have tried editing some syntax to

=SUMIFS(INDIRECT("X6"),indirect("U6")&"!"&A4:A99," ="&P6,indirect("U6")&"!"&A4:A99,"<="&R6)

In the formula wizard it no longer reports any argument as invalid but when I run the function it tells me that there is an error in my formula.

There is another function that is easier to use: SUMPRODUCT().

Try this:
=SUMPRODUCT(INDIRECT(X6),(INDIRECT(""&Summary!U6&" !A4:A99")=Summary!P6)*1,(INDIRECT(""&Summary!U6&" !A4:A99")<=Summary!R6)*1)

If X6 contains the name Sec3Monday2, for instance, for Section 2 on Monday with 2 lanes closed, then SUMPRODUCT() will multiply the cost array for that condition by the 0's and 1's created by the time comparisons. You can easily build this up to multiply more than three arrays, so long as all arrays are the same dimension.

For INDIRECT(), you don't need the double quotes.

WPierce

Hokie,

The correct use of INDIRECT() would look like this:
=INDIRECT(""&U6&"!A4")=P6

Notice that the contatenated string uses double quotes, but the comparitor does not. Starting with the quotes forces the whole thing to evaluate into a string and allows the "short hand" contatenation.

After sleeping on it, I decided that it shouldn't be that difficult to use SUMIFS() instead of SUMPRODUCT(). So I tried again this morning and received the same answer with both. Yeah!

Be sure to account for equal row numbers when applying SUMIFS(). I entered hours from A2 to A25, so to make SUMIFS() work I used this approach:
=SUMIFS(INDIRECT(X6),INDIRECT(""&U6&"!A2:A25"),"= "&P6,INDIRECT(""&U6&"!A2:A25"),"<="&R6)

You might want to determine whether using < instead of <= would be appropriate; at first glance that would seem to count the fee for the time period following the lane closure.

Regards,
WPierce