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!

 

[jasonb75]

Is the table sorted by date? If it is then you could try

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

 

[Vanilla_Excel]

Is the table sorted by date? If it is then you could try

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

Yes, the sheet is normally sorted by Due Date, but keep in mind dates will repeat within the Due Date column, but I'm not sure if that is relevant to the formula you provided. the result I got from the formula was #N/A.

 

[jasonb75]

Is there a match for the start date in the table? I didn't allow for there not being one but as long as there is it should work, even if the row with that date is hidden.

Do either

=MATCH(A146,Table6[Due Date],0)

or

=MATCH(B146,Table6[Due Date],1)

return #N/A

 

[Vanilla_Excel]

Is there a match for the start date in the table? I didn't allow for there not being one but as long as there is it should work, even if the row with that date is hidden.

Do either

=MATCH(A146,Table6[Due Date],0)

or

=MATCH(B146,Table6[Due Date],1)

return #N/A

There is currently no match for the start date. I changed one of the dates to match and the formula worked! But not sure how to have the formula work if start or end dates do not have a match. . . the "Do Either", "or" confused me as it looks exactly like what is currently in your formula. . . . unless I'm missing something.

 

[jasonb75]

They are exactly the same as in the formula, it was to test and see which part was causing the problem. I was assuming the first part but it could have been both, which would have suggested a problem with the formatting of the table.

As you're using office 365, you should have the XMATCH function which has better options for approximate matching. This should work with the start date missing from the table.

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

 

[Vanilla_Excel]

They are exactly the same as in the formula, it was to test and see which part was causing the problem. I was assuming the first part but it could have been both, which would have suggested a problem with the formatting of the table.

As you're using office 365, you should have the XMATCH function which has better options for approximate matching. This should work with the start date missing from the table.

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

THANK YOU! It works beautifully.

 

[Vanilla_Excel]

They are exactly the same as in the formula, it was to test and see which part was causing the problem. I was assuming the first part but it could have been both, which would have suggested a problem with the formatting of the table.

As you're using office 365, you should have the XMATCH function which has better options for approximate matching. This should work with the start date missing from the table.

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

After spending sometime with the formula on my actual workform, I realized that it is not capturing some hours with duplicate Due Dates depending on the date range. For instance, 9/1/2021-9/30/2021 will total less hours than 8/31/2021-10/1/2021, even when August and October has already been filtered out. It seems that if I have multiple 9/10/2021 due dates, it only captures the 1st value, and the values below are not captured. Sorry to trouble you again, but any ideas? I tested it by setting my start date and the end date as the same date.

 

[Vanilla_Excel]

I'd also like to add that it will capture multiple values with the same date when the end date only has 1 occurrence, so even if the start date has multiple occurrences, it will still be counted. So it seems to be not summing the end date due date when the end date has multiple occurrences.

 

[jasonb75]

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)))