sumproduct using dates and text...

[Hoss97]

The formula below is what I am trying to use to find the number of repairs that are late, based on today's date. Column F is formated as a date and has dates or blanks, column C is formated as general and has text or blanks, and column d is formated as general and has numbers or blanks. The formula as written is returning the #value error, but I can't seem to track what the problem is. I think I'm close, but have a comma, or something in the wrong place. Any help is greatly appreciated.

=SUMPRODUCT(--('Late Orders - Roy''s Report'!$F$3:$F$300<=$B$1),--('Late Orders - Roy''s Report'!$C$3:$C$300="repair"),--('Late Orders - Roy''s Report'!$D$3:$D$300))

 

[Jonmo1]

Are there pre existing value errors in any of the 3 referenced ranges ?
That will cause sumproduct to return the same error...

 

[Hoss97]

=IF(ISNA(VLOOKUP($A91,'Import List Here'!$A:$P,14,0)),"",VLOOKUP($A91,'Import List Here'!$A:$P,14,0))

This is the formula that I have in the F range. I changed the sumproduct formula to this:

=SUMPRODUCT(--('Late Orders - Roy''s Report'!$F$3:$F$90<=$B$1),--('Late Orders - Roy''s Report'!$C$3:$C$90="repair"),--('Late Orders - Roy''s Report'!$D$3:$D$90))

This formula works and returns the correct value. Thank you for catching this.

After row 90 the rows are blank, but contain the first formula listed. My problem is, today, data fills 90 rows, tomorrow there may be data in 105 rows, next week, data may be in 200 rows. I'd like to have a formula that will work, no matter how many rows in the range have data. Is this possible?

 

[Jonmo1]

After closer examination, I see the problem...
It doesn't matter if there are blanks or even formula blanks in the range...

=SUMPRODUCT(--('Late Orders - Roy''s Report'!$F$3:$F$90<=$B$1),--('Late Orders - Roy''s Report'!$C$3:$C$90="repair"),--('Late Orders - Roy''s Report'!$D$3:$D$90))

You only need to use the --(..) when applying a criteria expression to the range..
You don't do that on the range to sum...

Try just

=SUMPRODUCT(--('Late Orders - Roy''s Report'!$F$3:$F$300<=$B$1),--('Late Orders - Roy''s Report'!$C$3:$C$300="repair"),'Late Orders - Roy''s Report'!$D$3:$D$300)


Hope that helps.

 

[Hoss97]

That works!!! Thank you jonmo1!! That makes life a lot easier!!

 

[Jonmo1]

Glad to help, thanks for the feedback.