-1

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

paul
  • 59
  • 5
  • Have a look at `COUNTIFS`: – Qualia Communications Jul 05 '21 at 21:28
  • I thought about COUNTIFS but how i count per client? i mean the first criteria will be expense >=50 and the range is A2:A20000, but how i can count relation to client_id ? – paul Jul 05 '21 at 21:38
  • so i dealt with that. now i need grouping those numbers per day-group, 0-30 days, 30-60 days, 60-90 days. i am trying use COUNTIFS function again, but i get 0. =COUNTIFS($G$2:$G$2590,"<30",$G$2:$G$2590,">=30" & "<60", $G$2:$G$2590, ">=60" & "<90",$G$2:$G$2590) is this right ? – paul Jul 05 '21 at 22:18
  • It is not really clear based on the example data and question what you are looking for and what the data is like. Isolating just the 0-30 days case: do you want the number of unique client_id's that spend more than 50 at any time in the first 30 days? Or do you want the total number of times a given unique client_id spends more than 50 in the first 30 days? Is it 30 days from 1/1/2020? If you could edit your question to give data and expected results for a small case it would help a lot. I agree with the prior comment that COUNTIFS will likely be the answer – EDS Jul 05 '21 at 23:39
  • I will explain myself again. Suppose I have customer number 1, as of 01/01/2020 he spends money. Only once a day for any amount, there may be a day when he does not spend money. Once his ID appears in the customer column, I start checking how much money he has spent per day and if there is a continuum of days in which he has spent money each day over a certain amount, I count those days and then belong to groups. If he spent 40 days in a row, I assign him to a group of 30-60 days. Then the group gets the value 1. If spent in a sequence of 20 days, I assign it to the group of 0-30 days. – paul Jul 06 '21 at 07:28
  • And this is how I do for all customers. Finally each group of days will have values, which are sums of the ones – paul Jul 06 '21 at 07:28
  • So you want to count how many **consecutive** days a client spent at least 50? And then, depending on the quantity of consecutive days, the client will be clasified into group 0-30, 31-60, and so on? – Foxfire And Burns And Burns Jul 06 '21 at 07:57
  • yes, you are right – paul Jul 06 '21 at 09:16
  • But in that case, it could be that a client appears in 2 or more different categories at the same time. If the client spents 50 dollars for 20 days, then 10 days nothing, then 40 days another 50 dollars each day, the client would appear in group 0-30 and also 31-60? – Foxfire And Burns And Burns Jul 06 '21 at 10:07
  • its ok, it could happend – paul Jul 06 '21 at 10:19

2 Answers2

1

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.

Qualia Communications
  • 715
  • 6
  • 11
0

how to count values in K4 ? =COUNTIFS($H:$H,">="&J$2,$H:$H,"<"&K$2)

user16413979
  • 1
  • I assume that your question is rather a comment on my answer than an answer to the initial question. You can just copy the formula from `J4` to the right. For `K4` it would be: `=COUNTIFS($H:$H,">="&K$2,$H:$H,"<"&L$2)`. – Qualia Communications Jul 09 '21 at 21:01