Sumif and Subtotal

[djcfisher]

Hi, I've gone beyond my ability with the attached, so grateful for any help. In cell H5 of the 'Order Form' worksheet, I'm trying to get an accurate calculation for the number of egg boxes to be delivered to customers this week. The source data for the calculation is on the Customers worksheet, where Column J shows how many boxes each customer wants (a box costs £1.25 so £2.50 = 2 boxes, etc.). Any character whatsoever in 'Customers' Column K means that the customer is on holiday and therefore their box/es should not be counted. Lastly, 'Customers' is filtered and I want the total in 'Order Form' H5 to count only the visible customers. I know that somehow I need to combine Subtotal and Sumif, but I can't find a solution. Thanks for your help!Sample customer spreadsheet.xls

[TMS]

SUBTOTAL(101,...) is for an Average, hence the #DIV/0! error for Half Standard ... no entries in the range.

SUBTOTAL(102,...) is a COUNT
SUBTOTAL(109,...) is a SUM

From the Help on the function:

I don't really understand your layout or logic so I can't really offer much more help.

Regards, TMS

Regards, TMS

[benishiryo]

hi djcfisher, perhaps you can do a SUMIFS or SUMPRODUCT formula that is according to your filter instead of using SUBTOTAL. something like this:
=SUMPRODUCT((J6:J89)*(K6:K89="")*(O6:O89="")*(L6:L89={"1,2","1,2,3,4"}))/1.25

[djcfisher]

Hi benishiryo, thanks for the suggestion ... that works well when that particular filter is applied ... is there a simple way to get it to respond when a different filter is applied (e.g. {"1,2","1,3"} etc.?). The filter changes every week. Thanks very much for your help!

[benishiryo]

sure thing. place somewhere in your main worksheet a place to put criteria horizontally, say in O1:R1 and formulate as such:
=SUMPRODUCT((J6:J89)*(K6:K89="")*(O6:O89="")*(L6:L89=O1:R1))/1.25

only put in there what you want to see. in you scenario, just "1,2" & "1,3" without the double quotes.