+ Reply to Thread
Results 1 to 8 of 8

Count number of cells in a column that do not match records in a seperate range

  1. 05-09-2013, 08:11 PM #1
    ports83
    ports83 is offline
    Registered User
    Join Date
    09-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Count number of cells in a column that do not match records in a seperate range

    Hi Guys,
    I'm having a bit of an issue that i hope you can help with. I'm trying to do a count of cells that do not contain text that is in an exception list.

    ie;

    Sheet1 Sheet2
    I N
    Apple Apple
    Bananna Orange
    Mango
    Apple
    Apple
    Orange
    Grape
    Apple
    Grape


    So essentially i want to count the number of cells in column I on sheet1 that do not contain records in a range in column N on sheet2. This is very basic though, as my current data set on sheet1 is 2700 rows long and i currently have 26 exceptions, though it is possible they may change.
    Last edited by ports83; 05-12-2013 at 11:37 PM.
    Reply With QuoteRegister To Reply
  2. 05-09-2013, 09:34 PM #2
    benishiryo
    benishiryo is offline
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Count number of cells in a column that do not match records in a seperate range

    hi ports83. assuming data in I2:I20, try:
    =ROWS(I2:I20)-SUMPRODUCT(COUNTIF(I2:I20,Sheet2!N2:N3))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."

    Tips & Tutorials I Compiled | How to Get Quick & Good Answers
    Reply With QuoteRegister To Reply
  3. 05-09-2013, 09:59 PM #3
    ports83
    ports83 is offline
    Registered User
    Join Date
    09-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count number of cells in a column that do not match records in a seperate range

    Thanks for that... I can't use the first half as I don't know what my range is as the nr of rows always varies, however I can utalise one of my existing formulas and add -SUMPRODUCT(COUNTIF(I2:I20,Sheet2!N2:N3)) to get the figures I require.
    Reply With QuoteRegister To Reply
  4. 05-09-2013, 10:51 PM #4
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count number of cells in a column that do not match records in a seperate range

    If I understand what you're wanting to count, try this...

    Formula entered on sheet1:

    =SUMPRODUCT(--ISNA(MATCH(I2:I10,Sheet2!N2:N3,0)))
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  5. 05-12-2013, 06:43 PM #5
    ports83
    ports83 is offline
    Registered User
    Join Date
    09-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count number of cells in a column that do not match records in a seperate range

    Hi Tony,
    That formula works as well which is great... I now have this working in my spreadsheet, but have found I need to expand on it....

    I'm trying to do a COUNTIFS, to count the number of exceptions (which i now have thanks to your responses), where their last updated status is YES and update by a person....

    Previously i was looking at individual values, so COUNTIFS(Sheet1!M:M, "Yes", Sheet1!:P:P, "Person", Sheet1!I:I, "Grape") - so counting the number of instances Grape is found when the value in column M is "Yes", and the value in column P is a specific person. I'm now trying to do something similar but with the exceptions, instead of a single item but am coming up short... any ideas?
    Reply With QuoteRegister To Reply
  6. 05-12-2013, 10:55 PM #6
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count number of cells in a column that do not match records in a seperate range

    Not sure I follow you on that.

    Maybe something like this...

    =SUMPRODUCT(--(Sheet1!M2:M10="Yes"),--(Sheet1!P2:P10="Person"),--ISNA(MATCH(Sheet1!I2:I10,N2:N3,0)))
    Reply With QuoteRegister To Reply
  7. 05-12-2013, 11:37 PM #7
    ports83
    ports83 is offline
    Registered User
    Join Date
    09-05-2012
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    23

    Re: Count number of cells in a column that do not match records in a seperate range

    Hi Tony,
    That works perfectly! Thanks heaps
    Reply With QuoteRegister To Reply
  8. 05-13-2013, 08:51 AM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Count number of cells in a column that do not match records in a seperate range

    You're welcome. Thanks for the feedback!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1