+ Reply to Thread
Results 1 to 12 of 12

Count function to count number of clients who received service in a given month

  1. #1
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Count function to count number of clients who received service in a given month

    Good morning!

    I am needing a function to count the number of unique clients who receive services in a given month. To do this I need to know the number of unique IDs in column A, for a selected month (column E value selected in N3) and selected FY in Column G (Column G value selected in M23).

    Thank you for your help!
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Count function to count number of clients who received service in a given month

    how about
    =ROWS(UNIQUE(FILTER(A2:A22,(E2:E22=N3)*(H2:H22=M23))))
    and selected FY in Column G (Column G value selected in M23).
    column h is fy22
    so maybe change above to work

    see T2
    Attached Files Attached Files
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Count function to count number of clients who received service in a given month

    Thank you. That got me the total number of rows (21), but I need to know the number of unique clients (should be 8). Also, I forgot that I need to also filter the results by the Agency selection in N2.

  4. #4
    Forum Expert wk9128's Avatar
    Join Date
    08-15-2020
    Location
    Australia
    MS-Off Ver
    365 V2403 and WPS V2022
    Posts
    3,629

    Re: Count function to count number of clients who received service in a given month

    Cell N11 formula , Drag down

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

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Count function to count number of clients who received service in a given month

    i get 2 ids for
    month 12 = column E , criteria N3 =12
    FY22 = column H , criteria M23 = FY22

    results 2 ID

    adding column B with criteria N2
    =ROWS(UNIQUE(FILTER(A2:A22,(B2:B22=N2)*(E2:E22=N3)*(H2:H22=M23))))
    = calc - no results

    the 8 is just unique ID in the full range with no criteria

  6. #6
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Count function to count number of clients who received service in a given month

    Thank you! That worked!

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Count function to count number of clients who received service in a given month

    what worked , just so others no the solution which solved the question

  8. #8
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Count function to count number of clients who received service in a given month

    The solution that worked was =ROWS(UNIQUE(FILTER(A2:A22,(B2:B22=N2)*(E2:E22=N3)*(H2:H22=M23)))).

    I tested =SUM(SUMIFS($K$2:$K$22,$A$2:$A$22,UNIQUE($A$2:$A$22),$E$2:$E$22,$N$3,$G$2:$G$22,$N$4,$H$2:$H$22,$M11)), but this gave the total services provided, not the unique number of clients.

    Thank you

  9. #9
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Count function to count number of clients who received service in a given month

    How would I adapt the Rows function to calculate the number of unique clients served year to date based on the fiscal year month N4? Same concept, but if FYMonth 3 is selected, I would want to add the number of clients from FY Months 1, 2, and 3?

  10. #10
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Count function to count number of clients who received service in a given month

    not fully understand - but rather than = N4 ?
    anyway - rather than =N4 then <=

    =ROWS(UNIQUE(FILTER(A2:A22,(B2:B22=N2)*(E2:E22=N3)*(H2:H22=M23)))).
    where is N4
    anyway
    =ROWS(UNIQUE(FILTER(A2:A22,(B2:B22=N2)*(E2:E22<=N3)*(H2:H22=M23)))).

  11. #11
    Registered User
    Join Date
    05-06-2024
    Location
    Kansas City, MO
    MS-Off Ver
    Office 365
    Posts
    9

    Re: Count function to count number of clients who received service in a given month

    Thank you. Adding the <= worked. I appreciate your help this morning!

  12. #12
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.85 (24051214))
    Posts
    8,844

    Re: Count function to count number of clients who received service in a given month

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 04-16-2015, 05:48 PM
  2. How to count the number of unique clients per year?
    By VBAhelp3456 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-03-2014, 06:11 AM
  3. What is the best way to count the number of clients from a table?
    By VBAhelp3456 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-26-2014, 04:40 AM
  4. count number of clients by end of programme date or other criteria
    By john dalton in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-02-2013, 11:07 AM
  5. finding the count and sum of a transaction done in a month among 50000 clients
    By cvsuryam in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-23-2011, 08:33 AM
  6. Replies: 0
    Last Post: 11-18-2007, 07:24 PM
  7. Function to count Number of weekday in a Month
    By thuanpham in forum Excel General
    Replies: 4
    Last Post: 05-21-2005, 09:19 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1