+ Reply to Thread
Results 1 to 7 of 7

Count cells from range that differ from another range based on multiple criteria

  1. #1
    Registered User
    Join Date
    11-10-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Count cells from range that differ from another range based on multiple criteria

    I am trying to return a count of values any given date (Testing!D$2), only if the first 8 characters from a column on one sheet differ from the first 8 characters on another column on another sheet. Additionally it must not county any with the value "deferred" in a specific column. Essentially, I have a row of dates and need to tally only values which are unique to the second sheet (domestic 2) and not already present on the first sheet (domestic 1). My definition of unique is having first 8 characters (left function) that are not already present on the first sheet. I know the not function cannot be used in countifs scenario. I have have used the following formulas but found no success:

    =SUMPRODUCT(--('Domestic 2'!$J$2:$J$1000=Testing!D$2),--((LEFT('Domestic 2'!$A$2:$A$1000,8))<>(LEFT('Domestic 1'!$A$2:$A$1000,8))))
    {=SUM(((LEFT('Domestic 2'!$A$2:$A$1000,8)<>LEFT('Domestic 1'!$A$2:$A$1000,8)))*('Domestic 2'!$J$2:$J$1000=Testing!D$2))}

    Additionally I do not understand why this function does not work or how I can make it work. I know the issue is in the last range/criteria:
    =COUNTIFS('Domestic 2'!$J$2:$J$1000,Testing!D$2,'Domestic 2'!$C$2:$C$1000,"<>Deferred",(LEFT('Domestic 2'!$A$2:$A$1000,8),"<>"&LEFT('Domestic 1'!$A$2:$A$1000,8)))

    Really appreciate any help!
    Blake

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Count cells from range that differ from another range based on multiple criteria

    Maybe if you could upload a sample WB with data approximating your real sheet, and some examples in it showing what you expect, we can figure something out for you

    P.S.
    please show what your formulas are giving as well...sometimes it as simple as using ABSOLUTE referencing instead of RELATIVE referencing

    Hope this helps
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Count cells from range that differ from another range based on multiple criteria

    -Edit-
    Double Post
    Last edited by dredwolf; 11-10-2012 at 09:31 PM.

  4. #4
    Registered User
    Join Date
    11-10-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count cells from range that differ from another range based on multiple criteria

    Quote Originally Posted by dredwolf View Post
    Maybe if you could upload a sample WB with data approximating your real sheet, and some examples in it showing what you expect, we can figure something out for you

    P.S.
    please show what your formulas are giving as well...sometimes it as simple as using ABSOLUTE referencing instead of RELATIVE referencing

    Hope this helps
    Thanks for the respose. I have attached a workbook that mocks the scenario, my desired outcome along with 3 attempts. Using F9 I can tell that the true/false they are delivering is not what I need. For example, I need it to check if the first 7 characters of C2 are present anywhere in A2:A12, not just in A2.

    Please take a look. I am surprised I am so stumped on this one.

    Thanks,
    BlakeCountExcelHelp.xlsxCountExcelHelp.xlsx

  5. #5
    Registered User
    Join Date
    11-10-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count cells from range that differ from another range based on multiple criteria

    Quote Originally Posted by dredwolf View Post
    Maybe if you could upload a sample WB with data approximating your real sheet, and some examples in it showing what you expect, we can figure something out for you

    P.S.
    please show what your formulas are giving as well...sometimes it as simple as using ABSOLUTE referencing instead of RELATIVE referencing

    Hope this helps
    Thanks for the respose. I have attached a workbook that mocks the scenario, my desired outcome along with 3 attempts. Using F9 I can tell that the true/false they are delivering is not what I need. For example, I need it to check if the first 7 characters of C2 are present anywhere in A2:A12, not just in A2.
    CountExcelHelp.xlsx
    Please take a look. I am surprised I am so stumped on this one.

    Thanks,
    Blake

  6. #6
    Forum Expert icestationzbra's Avatar
    Join Date
    01-07-2004
    MS-Off Ver
    2007, 2010
    Posts
    1,417

    Re: Count cells from range that differ from another range based on multiple criteria

    put this in cell H3 and drag-fill towards right:

    Please Login or Register  to view this content.
    - i.s.z -
    CSE, aka Array aka { }, formulae are confirmed with CONTROL+SHIFT+ENTER.
    Replace commas ( , ) with semicolons ( ; ) in formulae, if your locale setting demands.
    All good ideas are courtesy resources from this forum as well as others around the web.
    - e.o.m -

  7. #7
    Registered User
    Join Date
    11-10-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Count cells from range that differ from another range based on multiple criteria

    Thanks so much icestationzbra! It all came together once I realized I was missing the MATCH. I used the following in my sheet but may switch over to your INDEX method to avoid using Array formula.
    Please Login or Register  to view this content.

+ 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