3

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.

enter image description here

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.

CallumDA
  • 12,025
  • 6
  • 30
  • 52
NotAnExpert
  • 33
  • 1
  • 1
  • 7
  • Perhaps you could include some of your data and show us what you are trying to do - i.e. what the formula should return – CallumDA Jan 11 '17 at 16:27
  • Use as array formula. Means Press `CTRL+SHIFT+ENTER` after entering formula to cell. – Harun24hr Jan 11 '17 at 16:56
  • So, what I've got is a list of machines, orders, quantities, and due dates. The idea is to filter the list on the due dates, and return the quantity of the orders occurring on a specific list of machines. I'm working on editing it into a the post. – NotAnExpert Jan 11 '17 at 19:00

1 Answers1

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: enter image description here

After applying a filter: enter image description here

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.

Rufus
  • 368
  • 1
  • 8
  • Thank you! The date filter varies weekly - it's for a report given to a very old-fashioned manager, by an old-fashioned scheduler - so I'll keep the date filter out of the formula. I appreciate your help! I searched all over the place for help with this, but couldn't find anything that used more than 1 criteria in the sumif. – NotAnExpert Jan 12 '17 at 14:41