Can Sumproduct check for dates greater than?

This is what I have:

SUMPRODUCT(--(D50>='Commercial Loans'!X3:X1999),--('Commercial Loans'!J3:J1999="Iris"),'Commercial Loans'!G3:G1999)

This formula is suppose to add everything in cell G3:G1999(commercial loans tab) if anything on cells X3:X1999(commercial loans tab) is less than or equal to cell D50(summary tab) that contain “Iris” on cells J3:J1999(commercial loans tab).

Its not working the way I’d like… & I don’t know why.

SUMPRODUCT(--(D50>='Commercial Loans'!X3:X1999),--(D50<'Commercial Loans'!I3:I1999),--(ISNUMBER(SEARCH("Iris",'Commercial Loans'!J3:J1999))),'Commercial Loans'!G3:G1999)

This one is suppose to add everything in cell G3:G1999(commercial loans tab) if anything on cells X3:X1999(commercial loans tab) is less than or equal to cell D50(summary tab) & if cells I3:I1999 are greater than D50(Summary tab) & of course that contain “Iris” in cells J3J1999(commercial tab)

Again not working properly… is it possible that instead of checking cells I3:1999 for dates that I check AK3:AK1999 to see if their blank??? If their blank then add if not then don’t….

If column I contains dates and blanks, you could get a miscount... try:


SUMPRODUCT(--(D50>='Commercial Loans'!X3:X1999),--('Commercial Loans'!I3:I1999>0),--(D50<'Commercial Loans'!I3:I1999),--(ISNUMBER(SEARCH("Iris",'Commercial Loans'!J3:J1999))),'Commercial Loans'!G3:G1999)

When Excel looks at cells to determine greater than or equal to a set criteria, it sees blanks as 0 and therefore they would count if you are looking for less than a set criteria.. so you have to exclude those blanks with that additional condition.