Hi I have a sheet setup to count and sum the values as below, second is an array formula.

=SUMPRODUCT(('Fert Input'!E$12:E$151=B8)*('Fert Input'!F$12:F$151='Fert Mix'!$V$4))+SUMPRODUCT(('Fert Input'!G$12:G$151=B8)*('Fert Input'!H$12:H$151='Fert Mix'!$V$4))

=SUM(IF('Fert Input'!E$12:E$151=B8,IF('Fert Input'!F$12:F$151=$V$4,'Fert Input'!C$12:C$151,0),0))+SUM(IF('Fert Input'!G$12:G$151=B8,IF('Fert Input'!H$12:H$151=$V$4,'Fert Input'!C$12:C$151,0),0))

These work well but I wish to add a 3rd and 4th formula to count and sum < or = to

=SUMPRODUCT(('Fert Input'!E$12:E$151=B8)*('Fert Input'!F$12:F$151<=$N$1))+SUMPRODUCT(('Fert Input'!G$12:G$151=B8)*('Fert Input'!H$12:H$151<=$N$1))

=SUM(IF('Fert Input'!E$12:E$151=B8,IF('Fert Input'!F$12:F$151<=$N$1,'Fert Input'!C$12:C$151,0),0))+SUM(IF('Fert Input'!G$12:G$151=B8,IF('Fert Input'!H$12:H$151<=$N$1,'Fert Input'!C$12:C$151,0),0))

How might I stop the formulas above counting blank cells it finds in columns F and H?

=SUMPRODUCT(('Fert Input'!E$12:E$151=B8)*('Fert Input'!F$12:F$151='Fert Mix'!$V$4))+SUMPRODUCT(('Fert Input'!G$12:G$151=B8)*('Fert Input'!H$12:H$151='Fert Mix'!$V$4))

=SUM(IF('Fert Input'!E$12:E$151=B8,IF('Fert Input'!F$12:F$151=$V$4,'Fert Input'!C$12:C$151,0),0))+SUM(IF('Fert Input'!G$12:G$151=B8,IF('Fert Input'!H$12:H$151=$V$4,'Fert Input'!C$12:C$151,0),0))

These work well but I wish to add a 3rd and 4th formula to count and sum < or = to

=SUMPRODUCT(('Fert Input'!E$12:E$151=B8)*('Fert Input'!F$12:F$151<=$N$1))+SUMPRODUCT(('Fert Input'!G$12:G$151=B8)*('Fert Input'!H$12:H$151<=$N$1))

=SUM(IF('Fert Input'!E$12:E$151=B8,IF('Fert Input'!F$12:F$151<=$N$1,'Fert Input'!C$12:C$151,0),0))+SUM(IF('Fert Input'!G$12:G$151=B8,IF('Fert Input'!H$12:H$151<=$N$1,'Fert Input'!C$12:C$151,0),0))

How might I stop the formulas above counting blank cells it finds in columns F and H?

Last edited: