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

1. ## 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).

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

=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

3. ## 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. ## Re: Count function to count number of clients who received service in a given month

Cell N11 formula , Drag down

Formula:
`Please Login or Register  to view this content.`

5. ## 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))))

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

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

Thank you! That worked!

7. ## 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. ## 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. ## 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. ## 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. ## 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. ## Re: Count function to count number of clients who received service in a given month

you are welcome

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