Need help with error

[DKRbella0814]

Can someone help me understand why the first formula works fine and the second is returning an error? (#REF!)

SUMPRODUCT(('OnTime (2)'!$A$2:$A$9981=$A5)*('OnTime (2)'!$G$2:$G$9981<>"")*('OnTime (2)'!$G$2:$G$9981="Aug-10"))



ERROR:

SUMPRODUCT(('OnTime (2)'!$A$2:$A$9981=$A5)*('OnTime (2)'!$G$2:$G$9981<>"")*('OnTime (2)'!$G$2:$G$9981="Aug-10")*('OnTime (2)'!$N$2:$N$9981="On Time"))

 

[Dryver14]

I ahvent run the formulas but should the last reference be

$N$2:$G$9981="On Time"))

You have N in second part

$N$2:$N$9981="On Time"))

 

[Jonmo1]

The only difference between the 2 formulas is the last argument in the 2nd
('OnTime (2)'!$N$2:$N$9981="On Time")

And the 1st formula works, the 2nd returns #REF!

Therefor, the only reasonable explaination is there are other formulas within the range
'OnTime (2)'!$N$2:$N$9981
That result in #REF!

Hope that helps.

 

[DKRbella0814]

No, Column N is the only column that has "On Time" or "late"

I basically want a formula that will add the number of "On Time" occurrences (col N (N2:N4000) if it also matches the designated month (Aug-10) which is in col G (g2:g4000) and if it also matches the correct vendor in col A (a2:a4000)

*Range has been simplified