COUNTIFS-Want to remove several words in one column w/multi criter
 

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-Want to remove several words in one column w/multi criter
 

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)

COUNTIFS-Want to remove several words in one column w/multi criter
 

On Sat, 21 Feb 2009 10:35:01 -0800, 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)


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

COUNTIFS-Want to remove several words in one column w/multi criter
 

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