Search code examples
excelexcel-formulacountexcel-2019

How to count appearences along a timeline with criteria

I have a table with 3 columns: expense, client_id, date. The date has record of clients expense for half a year for each day. Some clients spend many each day, but some not. The expendure ranging from zero to few hundreds. I want to group/count active client, which spend over 50 dollars each day, and calculate how much clients was active between 0-30 days, 30-60 days, 60-90 days, 90-120 days, 120-150 days, 150+. I mean if client spend at least 50 dollars each day along 40 days, i add him to 30-60 days column. client id appears only once each day.

expense client_id date
20 1 01/01/2000
60 2 01/01/2020
70 3 01/02/2020

the result should be like that

0-30 days 30-60 days 60-90 days 90-120 days
9 3 12 20

the values are count of active clients Thank you a lot

Solution

  • There might be better solutions with less helper columns, but this is what I can offer at the moment.

    enter image description here

    Preparation

    I generated some random data in columns A to C for 10 clients, one month and an expense limit of 100. You will have to adjust the expense threshold and the day ranges to your needs.

    Helper columns and formulas

    • criteria_met checks whether the expense is higher or equal the given threshold (here 10). Formula in D2 is =IF(A2>=10,TRUE,FALSE).
    • is_consecutive checks whether the client had an entry on the previous day. Formula in E2 is =IF(COUNTIFS(B$1:B1,B2,C$1:C1,C2-1)>0,TRUE,FALSE).
    • consecutive_group assigns a number for each group of consecutive days, on which the client had an expenditure above or equal the threshold. Formula in F2 is =IF(AND(D2=TRUE,E2=TRUE),MAXIFS(F$1:F1,B$1:B1,B2),MAXIFS(F$1:F1,B$1:B1,B2)+1). Whenever the combination of criteria_met and is_consecutive is FALSE, the group number is increased by one.
    • days_per_group counts the number of days per client_id and consecutive_group. Formula in G2 is =COUNTIFS(B$1:B2,B2,F$1:F2,F2).
    • max_per_group makes sure that only the max number of consecutive days is considered per consecutive group. Formula in H2 is =IF(G2=MAXIFS(G:G,B:B,B2,F:F,F2),G2,0).

    Result table

    • labels creates the headline. Formula in J3 is =J2&"-"&K2-1&" days"

    • values counts how often a number between the given thresholds occured. Formula in J4 is =COUNTIFS($H:$H,">="&I$2,$H:$H,"<"&J$2).

    Requirements

    • Each client can only have one entry per day
    • The source list (A:C) has to be sorted by date

    Please let me know, if you need more information.