+ Reply to Thread
Results 1 to 5 of 5

Counting number of cells matching 2 criteria, one partial (, seperated values) & one full

  1. 06-08-2013, 01:34 AM #1
    pjbiju
    pjbiju is offline
    Registered User
    Join Date
    10-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Counting number of cells matching 2 criteria, one partial (, seperated values) & one full

    I have table that looks like the one given below
    . A B C D
    1 No TC Description DTC? TP
    2 1 Description 1 Yes 1
    3 2 Description 2 No 1,2
    4 3 Description 3 Yes 2,1
    5 4 Description 4 No 3
    6 5 Description 5 Yes 1,4
    7 DTC-Yes? TP No of TC
    8 DTC-Yes 1 ?
    9 DTC-Yes 2 ?
    10 DTC-Yes 3 ?
    11 DTC-Yes 4 ?


    The data that I have is from A1:D6.

    I need to fill columns D8:D11 (that I have marked with ? above). In each of these cells, excel should count the number of rows in the data that match for a "Yes" in C2:C6 and also match the TP (C8:C11) number in D2:D6. This column contains comma seperated numbers. For example, in D8 I should get the value 3 since there are three rows with "Yes" in column C2:C6 and with 1 present in D2:D6.

    I am able to get the number of rows that match with the TP number using the formula {=SUM(LEN(D2:D6)-LEN(SUBSTITUTE(D2:D6,C8,"")))/LEN(C8)}. This gives me 4 in D8. However I am not able to add the second criteria of "Yes" to this formula to get the result of 3 in D8.

    The input from people would be in C8:11. So the number added in this cell should be used for the partial criteria matching.

    Any help to solve this would be appreciated.
    Reply With QuoteRegister To Reply
  2. 06-08-2013, 01:46 AM #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: Counting number of cells matching 2 criteria, one partial (, seperated values) & one f

    hi pjbiju, welcome to the forum. try:
    =SUMPRODUCT(($C$2:$C$6="yes")*(ISNUMBER(SEARCH(C8,$D$2:$D$6))))

    Edit: to be safer, maybe:
    =SUMPRODUCT(($C$2:$C$6="yes")*(ISNUMBER(SEARCH(","&C8&",",","&$D$2:$D$6&","))))
    this will ensure if you have numbers like 10 inside D2:D6, it won't be counted as "1"
    Last edited by benishiryo; 06-08-2013 at 01:53 AM.

    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. 06-08-2013, 01:50 AM #3
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Counting number of cells matching 2 criteria, one partial (, seperated values) & one f

    Hi and welcome to the forum

    Try this, copied down...
    =COUNTIFS($C$2:$C$6,"yes",$D$2:$D$6,"*"&C8&"*")

    Well done Ben, My formula misses the 1 in row 2, because it's a value

    Edit, have to change my formula above to this (gets messy)...
    =COUNTIFS($C$2:$C$6,"yes",$D$2:$D$6,"*"&C8&"*")+COUNTIFS($C$2:$C$6,"yes",$D$2:$D$6,C8)
    Last edited by FDibbins; 06-08-2013 at 01:56 AM.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford
    Reply With QuoteRegister To Reply
  4. 06-08-2013, 02:03 AM #4
    pjbiju
    pjbiju is offline
    Registered User
    Join Date
    10-11-2012
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Counting number of cells matching 2 criteria, one partial (, seperated values) & one f

    Quote Originally Posted by benishiryo View Post
    hi pjbiju, welcome to the forum. try:
    ...
    Edit: to be safer, maybe:
    =SUMPRODUCT(($C$2:$C$6="yes")*(ISNUMBER(SEARCH(","&C8&",",","&$D$2:$D$6&","))))
    this will ensure if you have numbers like 10 inside D2:D6, it won't be counted as "1"
    Thank you benishiryo. This solved my issue. I did a couple of minutes of testing with various combinations and it seems to work perfectly.

    Quote Originally Posted by FDibbins View Post
    ...
    Edit, have to change my formula above to this (gets messy)...
    =COUNTIFS($C$2:$C$6,"yes",$D$2:$D$6,"*"&C8&"*")+COUNTIFS($C$2:$C$6,"yes",$D$2:$D$6,C8)
    Well, this too works perfectly. Thank you.

    -Biju
    Last edited by pjbiju; 06-08-2013 at 02:12 AM.
    Reply With QuoteRegister To Reply
  5. 06-08-2013, 01:52 PM #5
    FDibbins
    FDibbins is offline
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,929

    Re: Counting number of cells matching 2 criteria, one partial (, seperated values) & one f

    Happy to help and thanks for the feedback - I still prefer Ben's
    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)

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1