+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    02-21-2009
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2007
    Posts
    7

    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)
    Last edited by Nickelcell; 02-21-2009 at 08:16 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

    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.
    Last edited by DonkeyOte; 02-21-2009 at 05:08 AM.

  3. #3
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

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

    With COUNTIFS, if you want to exclude multiple terms then you can just use additional criteria, i.e.

    =COUNTIFS('Video Detail'!$N:$N, 'Feb Video Sales'!D$3,'Video Detail'!$U:$U,"<>TRANSFER",'Video Detail'!$U:$U,"<>NON PAY RESTART”,'Video Detail'!$U:$U,”<>NON-PAY RESTART”,'Video Detail'!$R:$R, 'Feb Video Sales'!$B$43)

    but SUMPRODUCT would probably be easier if you have a larger list......

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

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

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

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

  5. #5
    Registered User
    Join Date
    02-21-2009
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2007
    Posts
    7

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

    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!
    Last edited by Nickelcell; 02-21-2009 at 02:28 PM. Reason: adding attachmnet

  6. #6
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

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

    Something wrong with the quote symbols in the first version I posted. Try this version

    =COUNTIFS('Video Detail'!$N:$N, 'Feb Video Sales'!D$3,'Video Detail'!$U:$U,"<>TRANSFER",'Video Detail'!$U:$U,"<>NON PAY RESTART",'Video Detail'!$U:$U,"<>NON-PAY RESTART",'Video Detail'!$R:$R, 'Feb Video Sales'!$B$43)

  7. #7
    Registered User
    Join Date
    02-21-2009
    Location
    Bay Area, California
    MS-Off Ver
    Excel 2007
    Posts
    7

    Thumbs up Re: COUNTIFS-Want to remove several words in one column w/multi criteria

    It worked!!! Thank you "Big" Daddylonglegs!!!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1