What seems so simple got me blank today, need a sumproduct based on criteria(Month value). Refer below.



Del. Date Units Req. Hour value

15Feb 3,570 0.732
15Feb 86 0.570
15Feb 833 0.570
15Feb 306 0.570
15Feb 1,285 0.570
15Feb 552 0.570
18Feb 4,794 0.914
19Feb 1,326 0.626
27Mar 2,856 0.565
24Mar 4,590 0.565

Jan =SUMPRODUCT(B1:B10,D1:D10) IF A1:A10=Month(1)
Feb =SUMPRODUCT(B1:B10,D1:D10) IF A1:A10=Month(2)
Mar =SUMPRODUCT(B1:B10,D1:D10) IF A1:A10=Month(3)

Sample sheet attached.

The answer in G2 should be:

=SUMPRODUCT((MONTH($A$2:$A$20)=1) * ($B$2:$B$20) * ($D$2:$D$20))

You have to change the red value as you copy the formula down.

This version is a little harder to read, but at least is a single formula you can copy downward:

=SUMPRODUCT((MONTH($A$2:$A$20)=ROWS($A$1:$A1)) * ($B$2:$B$20) * ($D$2:$D$20))

But my ultimate answer would be to put actual date values in my SUM chart so I could use a single formula like this and copy it down and it would adjust itself:

=SUMPRODUCT((MONTH($A$2:$A$20)=MONTH(F2)) * ($B$2:$B$20) * ($D$2:$D$20))

Mmm, not so simple then after all.
Thanks JB, I'm working again!