Forum Discussion

DaveyBob's avatar
DaveyBob
Copper Contributor
Feb 19, 2025

Counting unique values with multiple criteria

I'm a pretty basic Excel user, and trying to gather some data about our application's clients. Suppose I have this:

 

MONTH IRRELEVANT1 CLIENT IRREL2 SITE STATUS

JAN X 1 Y 101 ACTIVE

JAN X 1 Y 102 CLOSED

JAN X 2 Y 201 ACTIVE

JAN X 3 Y 301 CLOSED

JAN X 3 Y 401 ACTIVE

JAN X 4 Y 402 CLOSED

JAN X 4 Y 403 ACTIVE

JAN X 4 Y 405 CLOSED

JAN X 4 Y 406 ACTIVE

JAN X 5 Y 501 ACTIVE

FEB X 1 Y 103 CLOSED

FEB X 2 Y 201 ACTIVE

FEB X 3 Y 301 CLOSED

FEB X 2 Y 202 ACTIVE

FEB X 1 Y 102 CLOSED

FEB X 2 Y 203 ACTIVE

MAR X 3 Y 302 ACTIVE

MAR X 4 Y 402 CLOSED

MAR X 5 Y 502 ACTIVE

MAR X 6 Y 601 CLOSED

MAR X 4 Y 403 ACTIVE

MAR X 3 Y 305 CLOSED

MAR X 2 Y 205 ACTIVE

MAR X 1 Y 102 ACTIVE

MAR X 2 Y 201 CLOSED

MAR X 3 Y 302 ACTIVE

(Note the "irrelevant" columns interspersed)

Questions I want to answer:

  • By month, what are the number of clients that have at least 1 active site
  • By month, what are the number of active sites (across all clients)

Every Monday, I'm going to be adding in about 10,000 rows. I had something working for both of these that was using a FILTER with multiple criteria (matching on MONTH and matching on STATUS=ACTIVE) but after about 3 months, when I load in new data, Excel runs out of resources with those filters. So that's not gonna work.

I got kind of close with this:

=COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE")

That generates a spill range:

1
1
1
2
1
0

I don't actually understand how the first 2 parameters work, TBH. Output seems to be saying "client 1 occurs 1 time, 2 occurs 1 time, 3 occurs 1 time, 4 occurs 2 times, 5 occurs 1 time, 6 occurs 0 times). So it's close...Note that I cannot do this:

=COUNT(COUNTIFS(C2:C27, UNIQUE(C2:C27), A2:A27, "JAN", F2:F27, "ACTIVE")). I have to generate the results into a table, and then count that. Not ideal.

Any Excel wizards out there able to point me in the right direction?

excel

3 Replies

  • Harun24HR's avatar
    Harun24HR
    Bronze Contributor
    Feb 20, 2025

    It seems you need GROUPBY() and PIVOTBY() functions. Try-

    =GROUPBY(HSTACK(A2:A27,C2:C27),C2:C27,COUNT,0,0,,F2:F27="ACTIVE")

    Count by month.

    =PIVOTBY(C2:C27,A2:A27,C2:C27,COUNT,0,0,,0,,F2:F27="ACTIVE")

     

    • DaveyBob's avatar
      DaveyBob
      Copper Contributor
      Feb 20, 2025

      Thanks much for the guidance! The piece I'm not sure how to address is the fact that this data grows by about 10,000 rows per week. It's built using Get Data > File > Folder. And then when a new file is dropped in there, I simply refresh and the new rows come in from the new file.

      How would I make this work with a growing data set?

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Feb 20, 2025

    An alternative could be Power Query with a Pivot Table. In the attached file you can add data to the blue dynamic table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table. Then you can right-click in any cell of the Pivot Table and select refresh to update the result of the Pivot Table.

    The data layout in the screenshot and in the attached file is for illustration. You can place the green result table and the Pivot Table in other worksheets.

     

Resources