Summing with a start/end date but visible cells only.

[Vanilla_Excel]

I have a simple spread sheet that has a list of production hours which can be filtered by location (LA,TJ,CH) and if event (y or n). each date technically represents a unique order, so the dates column is not generally filtered. Start date and end date is set for a future week to gauge production hours needed at a particular location.

I have tried both SUMIFS and SUMPRODUCT, but I can't seem to get the syntax to work.

=SUMIFS(Table6[Est. ProdHrs],Table6[Due Date],">="&A146,Table6[Due Date],"<="&B146)*SUBTOTAL(102,OFFSET(D2,ROW(Table6[Est. ProdHrs])-MIN(ROW(Table6[Est. ProdHrs])),0,1,1))

=SUMPRODUCT((Table6[Due Date]>=A146)*(Table6[Due Date]<=B146)*Table6[Est. ProdHrs])*SUBTOTAL(102,OFFSET(D2,ROW(Table6[Est. ProdHrs])-MIN(ROW(Table6[Est. ProdHrs])),0,1,1))

I've scoured Multiple sites (including this one) and videos and I'm at my wits end. I think I got close, but I can't find my error.

Thanks in advance for all help given!

 

[Vanilla_Excel]

Sorry, I forgot to add the last argument to the second xmatch function. I think that this should be correct now although I don't have time to test it at the moment.
Note that it will only work if the dates are sorted in ascending order as mentioned in earlier posts.

=SUBTOTAL(109,INDEX(Table6[Est. ProdHrs],XMATCH(A146,Table6[Due Date],1)):INDEX(Table6[Est. ProdHrs],XMATCH(B146,Table6[Due Date],-1,-1)))

That did it! Thanks again for your help, it is greatly appreciated!