How to combine subtotal and sumif with a range of

问题:

I'm trying to create a formula to combine a SUMIF (using a range of criteria) and a subtotal.

The SUMIF statement:

=SUMPRODUCT(SUMIF(E:E,O2:O21,G:G))

And where I've gotten on making that SUMIF subtotal:

=SUMPRODUCT(SUBTOTAL(9,OFFSET(G2,ROW(G2:G5000)-ROW(G2),0)),(E2:E5000=O2:O21)+0)

That last statement returns an #N/A error. I'd appreciate any input y'all can provide.

Thank you.

Example:

Order  | Date  | Mach  | Rem 
-------|-------|-------|-------
A      |01/20  | 01    | 1200
B      |01/11  | 02    | 400
C      |01/21  | 01    | 420
D      |01/28  | 04    | 1180
E      |01/20  | 01    | 1200
F      |01/11  | 04    | 400
G      |01/21  | 03    | 420
H      |01/28  | 04    | 1180

No t sure I got the table right, so here's an image of what I see.

What I'm trying to do is filter based on the date - say, all orders that occur before 1/21 - and then give the amount remaining in a list of machines.

So, if I wanted the total from Machine 1 and 4, the output from that table (with the date filter on) would be 2800, summing order A, D, E, and F. With the filter off it'd be 5580.

回答1:

Try this formula:

=SUMPRODUCT(NOT(ISERROR(MATCH($C:$C;J:J;0)))*SUBTOTAL(103;OFFSET(C1;ROW(C:C)-MIN(ROW(C:C));0));$D:$D)

Excel structure:

After applying a filter:

You can also include date criteria already in the formula:

=SUMPRODUCT(NOT(ISERROR(MATCH($C:$C;J:J;0)))*($B:$B<$L$1);$D:$D)

Where L1 is date criteria.

But, of course, if you need filter usage, use the first solution.