Sumproduct

[Ken]

A few days ago I received help with the following formula and it works fine

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000>DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"NA"))

That said... when I try to use the same formula with the < it doesn't work.
Should I not be able to do the following?

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000<DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"NA"))

Appreciate the help.

Ken

 

[bpeltzer]

Yes you should. But I'll take a guess that a bunch of blanks now meet the
criteria. After all, 0 is probably < Dashboard!AD3, and blank activity types
also meet the <> tests for each type of activity you're checking. So you may
just need to put some kind of qualifier in there to check for a legit date
(maybe even just >0).

 

[Ken]

Thanks again ... that worked! Say is there anyway I can just download what
you know so I don't have to keep bothering you?

This worked

=SUMPRODUCT(('DATA (RAW)'!BH2:BH5000<DASHBOARD!AD3)
*('DATA (RAW)'!N2:N5000<>"SUPPORT REQUEST")
*('DATA (RAW)'!N2:N5000<>"WORK ORDER")
*('DATA (RAW)'!AQ2:AQ5000<>"CALA")
*('DATA (RAW)'!AQ2:AQ5000<>"ASIA")
*('DATA (RAW)'!AQ2:AQ5000<>"EMEA")
*('DATA (RAW)'!AS2:AS5000<>"CA")
*('DATA (RAW)'!BH2:BH5000<>0))