Subtotal with Multiple SUMPRODUCT Criteria

[singerbatfink]

Hi, Im trying to add subtotal to my formula in column K & L rows 4 to 10 so the results only show for a selected date. The date can be selected from the dropdown in R1.
Can anyone help. Sample file attached.

Regards B

[Pete_UK]

Try this in K4:

=SUMPRODUCT(($G$18:$G$201="G/Grade 1")*($H$18:$H$201="Fixed Term Appointment")*($B$18:$B$201=$R$2)*($A$18:$A$201))

(changes in red).

Hope this helps.

Pete

[samba_ravi]

K4=SUMPRODUCT($A$18:$A$201,($G$18:$G$201=$H4)*($H$18:$H$201="Fixed Term Appointment")*($B$18:$B$201=$R$2))
M4=SUMPRODUCT($F$18:$F$201,($G$18:$G$201=$H4)*($H$18:$H$201="Fixed Term Appointment")*($B$18:$B$201=$R$2))
Try the above, Copy and paste towards down

[Pete_UK]

If you wanted to make it more generic, you could also make these amendments to K5:

=SUMPRODUCT(($G$18:$G$201=$H4)*($H$18:$H$201=SUBSTITUTE($H$3," Staff",""))*($B$18:$B$201=$R$2)*($A$18:$A$201))

and then you could copy it down without needing to change the grades each time.

Hope thi shelps.

Pete

[Bo_Ry]

Please try

=COUNTIFS($G$18:$G$201,$H4,$H$18:$H$201,"Fixed Term Appointment",$B$18:$B$201,$R$2)

or

=SUMIFS($F$18:$F$201,$G$18:$G$201,$H4,$H$18:$H$201,"Fixed Term Appointment",$B$18:$B$201,$R$2)

[singerbatfink]

Thank you for you help.

[singerbatfink]

Thanks Pete, much appreciated. Overthinking will be the death of me.

[Pete_UK]

You're welcome - thanks for the rep.

Pete