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)

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" <(E-Mail Removed)> wrote in message
news:5C65A6AD-5A64-4255-844E-(E-Mail Removed)...
>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)
>
>

On Sat, 21 Feb 2009 10:35:01 -0800, Mmichole
<(E-Mail Removed)> wrote:

>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)
>

Here's one that seems to work without all the sheet references. You should be
able to adapt it.

Note that if you are EXCLUDING certain fields, you should also exclude BLANKS
and the LABEL in the first column:

=COUNTIFS(U:U,"<>JOB",U:U,"<>",U:U,"<>TRANSFER",U:U,"<>NON PAY RESTART")

--ron

Hi,

You can use countifs for this but the setup would look like this instead of
what you are trying:

=COUNTIFS(A:A, F1,C:C,"<>TRANSFER",C:C,"<>Test",D, G1)

Note I changed the references so I could test more easily. I would also
modify this to

=COUNTIFS(A:A, F1,C:C,"<>"&H1,C:C,"<>"&H2,D, G1)

where H1 and H2 contain two of your conditions, then just add the third
condition.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Mmichole" wrote:

> 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)
>
>