Hi I am trying to apply formula for summation of "PCS" on the basis of Column "lot status" if "lot status" is "Recived" than summation of "PCS" will be done for "Total Rcvd"

I am applying formula as

=SUMPRODUCT(--(C10:C11="Recived"),SUBTOTAL(9,OFFSET(J10:J11,ROW(J10:J11)-ROW(J10),0,1)))

But I am getting #VALUE! After going on formula bar and pressing enter the value comes as expected.

I am applying formula using apache poi Can any body help me on this

7PPFl.png

=SUMPRODUCT(SUBTOTAL(9,OFFSET(J10,ROW(J10:J19)-ROW(J10),,1)),--(C10:C19="Recived")). This formula returns subtotal as integer value and --(C10:C19="Recived") as Array which gives result as #VALUE!. Any help on this???

Capture.PNG