COUNTIFS doesn't easily handle multiple array constants. It really depends
on what you're trying to do.

Use SUMPRODUCT instead.

Use cells to hold your criteria:

H1 = TRANSFER
H2 = NON PAY RESTART
H3 = NON-PAY RESTART

Then use this general syntax:

=SUMPRODUCT(--(A1:A10=D3),--(ISNA(MATCH(B1:B10,H1:H3,0))),--(C1:C10=B43))


--
Biff
Microsoft Excel MVP


"Mmichole" wrote in message
...
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 in
advance.

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)