sum(hlookup( with cell references instead of values for col_indexes

I can't post a workbook, javascript error tried on 2 machines 4 browsers, posting screenshots takes me to the forum's firewall provider sucuri (I know it's not my firewall...) who won't allow javascript apparently.

How about
=SUMIFS($C$11:$C$41,$A$11:$A$41,">="&LEFT(A43,2)+0,$A$11:$A$41,"<="&LEFT(A43,2)+6)

Did you try the formula in my post #3?

Try this
https://docs.google.com/spreadsheets...it?usp=sharing

The sum(hlookup with values in the {} doesn't work in google sheets, but reproduce the book in excel and it does work.

I did try the suggestions posted. don't think they fit what I'm trying to do, hence the focus on getting a sample up.

I can do this with a series of individual lookups in a hidden col, and a separate sum, but there's a few reasons I want to get it in a single cell if I can.

The formula I supplied in post#5 works with the file you uploaded in your op.
Are you actually trying to do this in Sheets or in Excel?

I guess the OFFSET and WEEKDAY functionS work in Google sheets, so, assuming dates starting from November 1st in cell A12 and down, try this in any blank cell and copy down:

Formula:

Please Login or Register  to view this content.

Another option for the yellow portion in your worksheet, cell C44 copied down:

Formula:

Please Login or Register  to view this content.

There was another bit to it that I'd got to work wrapped around a simpler lookup, that wouldn't work around fluff's formula, it just needed a tweak I wasn't seeing though so fluff's solution does work after all. grovel grovel fluff, and thanks muchly both.

Solved.

mildly intrigued if it's actually possible to do it the way I originally tried though - can you put cell refs in an array like that, if so, how. I guess that question's never getting an answer.

Definitely want to figure out why I can't post sample files, that's going to be a major handicap.

can you put cell refs in an array like that

Fraid not.

Glad to help & thanks for the feedback.