+ Reply to Thread
Results 1 to 7 of 7

count the number of times a string occurs in a range based on a condition

  1. 05-29-2012, 03:46 AM #1
    sshishirkumar
    sshishirkumar is offline
    Registered User
    Join Date
    02-15-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    17

    count the number of times a string occurs in a range based on a condition

    Please help.

    I am unable to count the number of times a string occurs in a range based on a condition. The constraint which I have is as below.


    Count the number of times "Passed to Training" occurs in column1 only if the corresponding field in column two is "Not Started"

    I have uploaded the file which I used. Please provide your valuable suggestions to achieve the requirement
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 05-29-2012, 03:54 AM #2
    tom1977
    tom1977 is offline
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: count the number of times a string occurs in a range based on a condition

    Hi try
    =SUMPRODUCT((Phase2="Not Started")*(Phase1="Passed to Training"))
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.
    Reply With QuoteRegister To Reply
  3. 05-29-2012, 03:55 AM #3
    martindwilson
    martindwilson is offline
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: count the number of times a string occurs in a range based on a condition

    in f11 =SUMPRODUCT((Phase1=E11)*(Phase2<>"notstarted")) note named ranges phase1/2 must be the same length or you'll have to use something like

    =SUMPRODUCT((A1:A70=E11)*(C1:C70<>"notstarted"))
    Last edited by martindwilson; 05-29-2012 at 03:58 AM.
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula
    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC
    Reply With QuoteRegister To Reply
  4. 05-29-2012, 03:56 AM #4
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,428

    Re: count the number of times a string occurs in a range based on a condition

    In Excel 2003 you can use:

    =SUMPRODUCT(--(Phase1="Passed to Training"),--(Phase2="Not Started"))

    In 2007, you could use:

    =COUNTIFS(Phase1,"Passed to Training",Phase2,"Not Started")


    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  5. 05-29-2012, 03:57 AM #5
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: count the number of times a string occurs in a range based on a condition

    Hi.

    Try this.

    =SUMPRODUCT((Phase1="Passed to Training")*(Phase2="Not Started"))

    Is this, works for you?
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  6. 05-29-2012, 03:58 AM #6
    sshishirkumar
    sshishirkumar is offline
    Registered User
    Join Date
    02-15-2011
    Location
    India
    MS-Off Ver
    Excel 2003
    Posts
    17

    Re: count the number of times a string occurs in a range based on a condition

    Thanks for the quick replies.
    Reply With QuoteRegister To Reply
  7. 05-29-2012, 04:00 AM #7
    Fotis1991
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: count the number of times a string occurs in a range based on a condition

    You are welcome from ALL of us, BUT....Please, don't post Twice the same question...!!

    http://www.excelforum.com/excel-work...condition.html
    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