Excel 2007 : COUNTIFS-Want to remove several words in one column w/multi criteria

Hello.

I am trying to use the COUNTIFS function w/multiple criteria in an Excel 2007 workbook to count sales for reps in a summary tab but want to remove 3 different words in one column from the count. I've tried several scenarios and continue to get an error message or 0 results. Below is an example of the data I'm using and the function that went awry. Please help! Thanks inadvance.

Clmn N Clmn R Clmn S Clmn T Clmn U
CREATE DTE SALESID CHK IN OPID JOB REASON
1/23/2009 40974 ARR 1PQH Install NON PAY RESTART
1/23/2009 16029 LOC L24M Install TRANSFER
1/23/2009 16029 Install TRANSFER
1/23/2009 40904 Install TRANSFER
1/26/2009 40981 Upgrade CUSTOMR CONTACT


Function that worked w/1 word:=COUNTIFS('Video Detail'!$N:$N, 'Feb Video Sales'!D$3,'Video Detail'!$U:$U,"<>TRANSFER",'Video Detail'!$R:$R, 'Feb Video Sales'!$B$43)

Function that didn't work w/ all words to exclude:=COUNTIFS('Video Detail'!$N:$N, 'Feb Video Sales'!D$3,'Video Detail'!$U:$U,<>{"TRANSFER","NON PAY RESTART”,”NON-PAY RESTART”},'Video Detail'!$R:$R, 'Feb Video Sales'!$B$43)

For inclusive conditions you can SUM the inline array approach... eg say you wanted to COUNTIF ONLY if U = TRANSFER or NON PAY RESTART then this would work:

Please Login or Register  to view this content.

You can't obviously adopt the same for exclusive conditions given the summation will not work... ie when you calc how many entries don't match TRANSFER then the NON PAY RESTART entries are returned as part of the count... similarly when you process NON PAY RESTART the TRANSFER entries are included in the count ... thus when the 2 results are summed you are effectively failing to exclude the records.

So how to surmount the problem...

Either

a) conduct a COUNT ignoring the U condition and from that subtract the result of another COUNTIFS this type adopting the SUM approach wherein the INCLUSIVE conditions are those entries you wish to exclude ...
ie COUNTIFS(ignoring U conditions) - SUM(COUNTIFS(...{exclude}))
- does that make sense ?

b) switch to a SUMPRODUCT

Please Login or Register  to view this content.

Note for the Sumproduct I reduced the ranges from entire column references... though in XL2007 you can use entire column references (N:N - you can't in earlier versions) you should still avoid doing so as performance will be affected -- keep range sizes to minimum viable size.

I hope that helps.

ddl, I can always rely on you to point out my shortcomings

I can't believe that didn't even occur to me

Thank you both for ur quick replies! I tried them both. Donkey, urs worked perfectly! You are awesome! Daddy, I would prefer to use urs since it has the entire column reference which eliminates adjusting range each time and the amount of data is not that large. However I received another error message. I have attached a snapshot of them for ur review. Any ideas on why or is it something that I am simply missing?

Thanks!