+ Reply to Thread
Results 1 to 11 of 11

How can I count the number of occurrences in a range of cells?

  1. 01-26-2019, 08:55 PM #1
    jdcortez
    jdcortez is offline
    Registered User
    Join Date
    09-06-2017
    Location
    portland, oregon
    MS-Off Ver
    2016
    Posts
    33

    How can I count the number of occurrences in a range of cells?

    I am looking to count the number of times a value is found in a range of cells between two dates. My range is not a table but has headers for "Date", "Time" and "Result". It stretches from 
    Please Login or Register  to view this content.
    . The image below shows how my range is laid out. I have tried the following formula: 
    Please Login or Register  to view this content.
    formulas.PNG

    I should get 
    Please Login or Register  to view this content.
    if I was counting the number of occurrences of Client Interested between 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by jdcortez; 01-27-2019 at 01:51 AM. Reason: uploaded workbook
    Reply With QuoteRegister To Reply
  2. 01-26-2019, 09:36 PM #2
    Ochimus
    Ochimus is offline
    Valued Forum Contributor
    Join Date
    01-16-2012
    Location
    England
    MS-Off Ver
    MS 365
    Posts
    1,397

    Re: How can I count the number of occurrences in a range of cells?

    jd,

    You need to put the dates in quote marks (e.g. =COUNTIF(G8:KT992,">=3/31/2019")

    Ochimus
    Reply With QuoteRegister To Reply
  3. 01-26-2019, 09:40 PM #3
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: How can I count the number of occurrences in a range of cells?

    It would be easier if you had uploaded the workbook.

    However try putting a , after the second KT992, and make the past condition

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  4. 01-26-2019, 09:41 PM #4
    jdcortez
    jdcortez is offline
    Registered User
    Join Date
    09-06-2017
    Location
    portland, oregon
    MS-Off Ver
    2016
    Posts
    33

    Re: How can I count the number of occurrences in a range of cells?

    Unfortunately that did not solve what I am trying to acheive
    Reply With QuoteRegister To Reply
  5. 01-26-2019, 09:48 PM #5
    jdcortez
    jdcortez is offline
    Registered User
    Join Date
    09-06-2017
    Location
    portland, oregon
    MS-Off Ver
    2016
    Posts
    33

    Re: How can I count the number of occurrences in a range of cells?

    I included a link to my workbook on google drive
    Reply With QuoteRegister To Reply
  6. 01-27-2019, 01:44 AM #6
    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: How can I count the number of occurrences in a range of cells?

    Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites
    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
  7. 01-27-2019, 01:52 AM #7
    jdcortez
    jdcortez is offline
    Registered User
    Join Date
    09-06-2017
    Location
    portland, oregon
    MS-Off Ver
    2016
    Posts
    33

    Re: How can I count the number of occurrences in a range of cells?

    i have uploaded my file
    Reply With QuoteRegister To Reply
  8. 01-27-2019, 04:02 AM #8
    FioreMatto
    FioreMatto is offline
    Registered User
    Join Date
    11-22-2018
    Location
    Roma,Italia
    MS-Off Ver
    2013
    Posts
    40

    Re: How can I count the number of occurrences in a range of cells?

    Good morning
    you can use two external cells to enter search ranges such as K2 and K3 and in the output cell

    =SUMPRODUCT(($G$8:$G$1000>=$K$2)*($G$8:$G$1000<=$K$3)*($I$8:$I$1000="Client Interested"))
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 01-27-2019, 05:15 AM #9
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: How can I count the number of occurrences in a range of cells?

    Please try at A2 for check date in column G, K, N and result at I, L, O respectively

    =COUNTIFS(contacts!G:M,">=1/1/2019",contacts!G:M,"<=3/31/2019",contacts!I:O,"Client Interested")

    P.S. correrct miss spell Client at I10,I11
    Attached Files Attached Files
    Last edited by Bo_Ry; 01-27-2019 at 06:11 AM. Reason: Change date to M/D/YYY
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  10. 01-27-2019, 06:02 AM #10
    Roel Jongman
    Roel Jongman is offline
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    Re: How can I count the number of occurrences in a range of cells?

    you can make that formula a little more flexible by putting the start and enddate in 2 cells and change the formula a bit.

    let say startdate in A1, enddate in A2
    then USE
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 01-27-2019, 11:51 AM #11
    jdcortez
    jdcortez is offline
    Registered User
    Join Date
    09-06-2017
    Location
    portland, oregon
    MS-Off Ver
    2016
    Posts
    33

    Re: How can I count the number of occurrences in a range of cells?

    Quote Originally Posted by Bo_Ry View Post
    Please try at A2 for check date in column G, K, N and result at I, L, O respectively

    =COUNTIFS(contacts!G:M,">=1/1/2019",contacts!G:M,"<=3/31/2019",contacts!I:O,"Client Interested")

    P.S. correrct miss spell Client at I10,I11
    Thank you! Does exactly what I was looking for!
    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)

Similar Threads

  1. How do I count the number of occurrences for a time range using CountIF?
    By KatMat232 in forum Excel General
    Replies: 8
    Last Post: 07-07-2017, 06:19 PM
  2. COUNTIF - Count occurrences of a multi word string in any order, against a range of cells
    By hawk83542 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-28-2013, 10:24 AM
  3. [SOLVED] Count Occurrences and Fill Cells with Results Using a Date Range
    By CWatsonJr in forum Excel General
    Replies: 20
    Last Post: 06-07-2012, 02:33 PM
  4. [SOLVED] Count occurrences of a text string in a range of cells
    By skysurfer in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-30-2012, 04:01 PM
  5. Count number of occurrences conditionally on adjacent cells
    By toehrtman in forum Excel General
    Replies: 1
    Last Post: 02-09-2012, 05:13 PM
  6. Formula to Count the Number of Occurrences of a Text String in a Range
    By poug1903 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-10-2011, 06:49 AM
  7. Formula to Count the Number of Occurrences of a Text String in a Range
    By poug1903 in forum Excel General
    Replies: 1
    Last Post: 09-18-2009, 04:15 PM

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