+ Reply to Thread
Results 1 to 8 of 8

Excel Assistance Needed - Counting Number of Instances

  1. 10-01-2016, 09:14 AM #1
    bdav1216
    bdav1216 is offline
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Excel Assistance Needed - Counting Number of Instances

    See the attached sample spreadsheet:

    I want to count the number of Customer (Column B) incidents. This number will increase as the same customer has an incident.

    Column C: Count ALL Customer Incidents regardless of date (Column A).
    Column D: Count ALL Customer Incidents in the current Month (Column A). As today is October 1st, all of the values should be '0'.
    Column E: Count All Customer Incidents in the last 2 months (October and September in this example).

    Two other things I would like to do:

    I would like to then add a chart to the right that counts the number of incidents, using Column C, but EXCLUDE blank or 'All' customers in Column B.

    Thank you for your help.
    Attached Files Attached Files
    Last edited by bdav1216; 10-01-2016 at 09:55 AM.
    Reply With QuoteRegister To Reply
  2. 10-01-2016, 09:17 AM #2
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,208

    Re: Excel Assistance Needed - Counting Number of Instances

    No file attached.
    Reply With QuoteRegister To Reply
  3. 10-01-2016, 09:56 AM #3
    bdav1216
    bdav1216 is offline
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Re: Excel Assistance Needed - Counting Number of Instances

    I have updated with the attachment.
    Reply With QuoteRegister To Reply
  4. 10-01-2016, 10:53 AM #4
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,208

    Re: Excel Assistance Needed - Counting Number of Instances

    In C2

    =COUNTIF($B$2:$B$17,$B2)

    in D2

    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY()))*($B$2:$B$17=$B2))

    in E2

    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY())-1)*($B$2:$B$17=$B2))

    Copy all down columns.
    Reply With QuoteRegister To Reply
  5. 10-01-2016, 10:59 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,055

    Re: Excel Assistance Needed - Counting Number of Instances

    In C2:
    =COUNTA(A:A)-1

    In D2
    =COUNTIFS(A:A,">="&EOMONTH(TODAY(),-1)+1)

    In E2:
    =COUNTIFS(A:A,">="&EOMONTH(TODAY(),-2)+1,A:A,"<="&TODAY())
    Attached Files Attached Files
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  6. 10-03-2016, 07:53 PM #6
    bdav1216
    bdav1216 is offline
    Forum Contributor
    Join Date
    03-19-2016
    Location
    Chicago, IL
    MS-Off Ver
    Office 2016 Professional
    Posts
    388

    Re: Excel Assistance Needed - Counting Number of Instances

    Hello,
    These formula's below are working well, just a few minor questions/updates.

    In C2
    =COUNTIF($B$2:$B$17,$B2)
    How would I modify these formulas (which is working perfectly) to exclude customers (Column B) who have a value of 'All'? Customers whose value is equal to blanks are already not counting (equal to '0') so that part is exactly what I want.

    in D2
    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY()))*($B$2:$B$17=$B2))
    How would I modify these formulas to exclude blank (no data) customers or customers whose value is equal to "All" (Column B)?

    in E2
    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY())-1)*($B$2:$B$17=$B2))
    How would I modify these formulas to exclude blank (no data) customers (Column B)?


    Copy all down columns.
    Reply With QuoteRegister To Reply
  7. 10-04-2016, 03:17 AM #7
    DannyJ
    DannyJ is offline
    Forum Contributor
    Join Date
    08-29-2012
    Location
    Slough
    MS-Off Ver
    Excel 2007
    Posts
    469

    Re: Excel Assistance Needed - Counting Number of Instances

    Quote Originally Posted by bdav1216 View Post
    In C2
    =COUNTIF($B$2:$B$17,$B2)
    How would I modify these formulas (which is working perfectly) to exclude customers (Column B) who have a value of 'All'? Customers whose value is equal to blanks are already not counting (equal to '0') so that part is exactly what I want.

    =COUNTIFS($B$2:$B$17,$B2,$B$2:$B$17,"<>All")

    I don't understand why you want this one though? That formula is not counting all customers, it's only counting the customers that match B2 onwards so you will duplicate a lot of the numbers.
    Reply With QuoteRegister To Reply
  8. 10-04-2016, 04:34 AM #8
    JohnTopley
    JohnTopley is offline
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,208

    Re: Excel Assistance Needed - Counting Number of Instances

    Try

    in C2

    =COUNTIFS(B:B,"<>All",B:B,"<>")-1

    in D2

    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY()))*($B$2:$B$17=$B2)*($B$2:$B$17<>"All")*($B$2:$B$17<>""))

    in E2

    =SUMPRODUCT((MONTH($A$2:$A$17)>=MONTH(TODAY())-1)*($B$2:$B$17=$B2)*($B$2:$B$17<>"All")*($B$2:$B$17<>""))
    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. [SOLVED] Counting Partial Number Instances Within a Set Number Length.
    By MurasakiK in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-23-2013, 01:56 PM
  2. [SOLVED] Counting number of instances by department.
    By adam_crowther in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-28-2013, 10:31 AM
  3. [SOLVED] COUNTIFS : counting instances of a number after a particular date
    By viridian in forum Excel General
    Replies: 5
    Last Post: 05-01-2012, 03:32 PM
  4. Counting the number of instances
    By Rhys24 in forum Excel General
    Replies: 9
    Last Post: 06-23-2011, 09:54 AM
  5. Help needed with Counting the number of instances between blanks cells?
    By Starbucks Junkie in forum Excel General
    Replies: 8
    Last Post: 03-19-2009, 05:51 AM
  6. Counting Number of Instances with Conditions
    By excalibur69 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 10-02-2008, 02:44 PM
  7. Counting the number of instances
    By jswarb001 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-09-2007, 08:24 AM
  8. Counting the number of instances of a code in a certain week
    By Tibbs in forum Excel General
    Replies: 2
    Last Post: 04-29-2005, 05:56 AM

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