+ Reply to Thread
Results 1 to 11 of 11

COUNTIF answer in another thread

  1. #1
    Registered User
    Join Date
    05-11-2007
    Posts
    12

    COUNTIF answer in another thread

    Hello -

    This thread - http://www.excelforum.com/showthread.php?t=623080 gives me a beautiful near perfect answer to a problem I have been wrestling with. Thanks to NBVC!

    I'd like to enhance it just a bit by inserting an additional comparison (matching) criteria column - sort of like an AND statement embedded in the If(Countif) construct. I'm guessing that simply using AND inside the Countif isn't proper usage.

    Can someone help me figure out the syntax for second set of match criteria into NBVC's wonderfully clear and simple IF(COUNTIF - statement?

    What a nice helpful board this is! Thanks.

  2. #2
    Forum Contributor
    Join Date
    08-28-2006
    Posts
    280

    Take a look here

    Not really sure what you want here. but I think you may be able to do what you want with SUMPRODUCT. Take a look at this site and see if it helps you.

    http://www.xldynamic.com/source/xld.SUMPRODUCT.html

    Dean

  3. #3
    Registered User
    Join Date
    05-11-2007
    Posts
    12

    Question More

    OK, let me see if I can be more clear.

    Table A - Column A has 20000 last names - all upper case.
    Table A - Column B has City
    Table B - Column A has 500 last names - all upper case
    Table B - Column B has City
    (Table B is from a completely different purposed source file)

    In Table A, I'd like a column C to show an "x" when Table B, Col A -and- Table B, Col B match data found in Table A column A -and- Table A column B. IOW, I'm trying to find people in Table B who are also in Table A.

    Question: Would this solution be better suited to a VLOOKUP in Table A?

    Thanks!

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Hi whftherb,

    I sensed my name being used...

    For matching 2 columns in the same row, the Countif() doesn't work so well...

    Instead try something like this:

    =IF(MATCH(1,(Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1)),"x","")

    Where Sheet2 contains your table "B" and table "A" is in the active sheet.

    You must confirm this formula with CTRL+SHIFT+ENTER keys not just ENTER whenever you enter it or update it..you'll see { } brackets appear around the formula if confirmed correctly
    Last edited by NBVC; 11-18-2007 at 10:13 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    05-11-2007
    Posts
    12

    Question More....

    Boy - this is interesting playing with this! These functions are really something...

    Unfortunately, MATCH sample you provided, it returns an x every time, even when the data is different. I've attached the trial worksheet, I've been playing with.

    I also have failed to think through that in Sheet2, when I have a name and a color (I kept the data simple for practicing in the attachment) I need to search through all of Sheet1:A1-A100 and it's corresponding neighboring cell at Sheet1:B1-B100 for all instances where B:A1 and B:B1 match the two over in Sheet1.

    Again, this is just "screaming" at me to use some sort of LOOKUP function but I can't get that to work either. I've even thought of trying to concatenate Sheet1:A1 and Sheet1:B1 into a cell at Sheet1:C1 and do the same over at Sheet2 so I could use your simple Countif. But that seems kind of extreme.

    MATCH seemed like the right answer. Am I at least on the right path???

    Thanks.
    Attached Files Attached Files

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Sorry I left out a function...the ISNUMBER function....


    =IF(ISNUMBER(MATCH(1,(Sheet2!A1:A10=A1)*(Sheet2!B1:B10=B1),0)),"x","")

    adjust ranges and confirmed with CTRL+SHIFT+ENTER

    Also... couldn't open your workbook...if you need further help save it as Excel 2003 file before zipping it.

    Edit:

    Actually, there is another, easier formula that you don't need to confirm as an array formula to use. It involves the Sumproduct() function as Dean England pointed you to...

    Try:

    =IF(SUMPRODUCT((Sheet2!A1:A100=A1)*(Sheet2!B1:B100=B1)),"X","")

    adjusting ranges to suit and hitting just Enter.
    Last edited by NBVC; 11-18-2007 at 11:17 PM.

  7. #7
    Registered User
    Join Date
    05-11-2007
    Posts
    12

    Question Even More...

    Got it (Actually, you did...) Sorry to be so unskilled with this...

    But -

    I'm beginning to see where my not thinking this through is a big part of the problem in terms of clearly idenitifying the issue.

    This function compares Sheet2:A1, Sheet2:B1 with its corresponding positioned cells at Sheet1:A1, Sheet1:B1. Fine and it does that well. In my real word case, Sheet1 could have 20000 rows while Sheet2 could have only 500 rows. The expectation is that many of the Sheet2 cell pairs won't have matches in Sheet1 - but - can't take that to the bank...I have to ensure they're found if they exist.

    Question: Let's assume the matching target cell pair just so happens to be positioned at Sheet1:A91, Sheet1:B91? This solution won't MATCH Sheet2:A1, Sheet2:B1 by "searching" through Sheet1:ColA paired with sister cells Sheet1:ColB and pull out the corresponding match, right?

    Isn't this then calling for a greater amount of programming sophistication (like VBA???) to search and put an x whenever and wherever a matching cell pair is found no matter where it is found in ColsA/B of Sheet1?

  8. #8
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229
    How about putting this in TableA C3. This formulation assumes a namedRange tableB is avaliable for use.

    =IF(INDEX(tableB,MATCH(A3,INDEX(tableB,0,1),0),2)=B3,"x","ng")

    If A!A3 is not on the list, it errors.
    If A!A3 is on the list, but A!B3 doesn't match with the other val , "NG"
    If it all matches "x"

    The main idea is that by using MATCH you don't have to check tableB colA, either it matches or it errors.

    This won't work if there are two "SMITH"'s in tableB.

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Quote Originally Posted by whftherb
    Got it (Actually, you did...) Sorry to be so unskilled with this...

    But -

    I'm beginning to see where my not thinking this through is a big part of the problem in terms of clearly idenitifying the issue.

    This function compares Sheet2:A1, Sheet2:B1 with its corresponding positioned cells at Sheet1:A1, Sheet1:B1. Fine and it does that well. In my real word case, Sheet1 could have 20000 rows while Sheet2 could have only 500 rows. The expectation is that many of the Sheet2 cell pairs won't have matches in Sheet1 - but - can't take that to the bank...I have to ensure they're found if they exist.

    Question: Let's assume the matching target cell pair just so happens to be positioned at Sheet1:A91, Sheet1:B91? This solution won't MATCH Sheet2:A1, Sheet2:B1 by "searching" through Sheet1:ColA paired with sister cells Sheet1:ColB and pull out the corresponding match, right?

    Isn't this then calling for a greater amount of programming sophistication (like VBA???) to search and put an x whenever and wherever a matching cell pair is found no matter where it is found in ColsA/B of Sheet1?
    The solution(s) I provided will look for matches within the entire range, not only at a specific location...

    So if you put the formula in Sheet2!C1 (in order to see if there is a matching pair in Sheet1 columns A&B, it will return an "x" if you have a match anywhere in the 2 columns (ie. if a match is found in Sheet1!A91 and Sheet2!A91.... you just need to set the range and sheetnames in the formula to encompass the whole table in the sheet you are comparing against.

    If you post an example workbook, I can show you.

  10. #10
    Registered User
    Join Date
    05-11-2007
    Posts
    12

    Wink My faith in computers is restored!!!

    Take a look! Works like a charm. Although this took some time (and a lot of smart talented people), it has saved me HUGE hours and thousands of printouts. IOW - I was cranking up to do this manually because I was losing hope.

    It's neat too because as I change values on SheetB, I can almost "see" it drilling down through the array on SheetA. There's this really subtle slight delay as it recalculates.

    Note I used a range name on SheetA because if I copied the formula down from SheetB:C3 to cells below, I was getting relative reference confusion, even if I tried absolute referencing (SheetA!:$A1:$A100). NamedRanges are easier to understand anyway. I'm sure it's just me getting used to Excel-07.

    SUMPRODUCT - eh? :-)

    Thanks to all especially NBVC... Wonderful solution!
    Attached Files Attached Files

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898
    Well congratulations for getting it working...I am glad to have helped....

    ...just a note to anyone looking at whftherb's sample spreadsheet...If you see the #NUM! error in column C...it is because whftherb designed his workbook in Excel2007 which allows full column references in the Sumproduct() function.... earlier versions must have defined range references like A1:A100, or you will get the #NUM! error.

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1