Count items sold per day, excluding weekend, and including present day, by selected period

Question

I have a table where column A is the date/time, and column B fruits sold. I need a formula that automatically counts how many fruits were sold per day. With the following additional conditions:

• I need the present-day included.
  
• I need weekends excluded.
  
• I also need this formula to respond to a menu with the desired time period.
• Days with "0" count should not appear.

Here is a sample data:

Date	Fruit	Period
20/10/2022	18
21/10/2022	15
22/10/2022	10
23/10/2022	4
[...]
27/11/2022	19
28/11/2022	21

I have this formula (not mine tbf):

=LET(d,HubLog[Date],s,INT(MIN(d)),e,INT(MAX(d)),days,SEQUENCE(e-s,,s),
 weekdays,FILTER(days,MOD(days,7)>1),qty, 
   COUNTIFS(HubLog[Date],">="&weekdays,HubLog[Date],"<"&weekdays+1),
 CHOOSE({1,2},weekdays,qty))

But it does not seem to be working properly here. Also, in another file I have it was counting but not including the present day and not responsive to the time period menu. The file below is a simplified version of my real file, but for some reason it does not even seem to work.

Is there anything that can be changed in the formula to achieve the requirements explained above?

Dummy file: https://1drv.ms/x/s!AhJ6NsWJczYBhSjKQVMab8WlYINT?e=uxZOJs

Answer 1

In cell E2 you can use the following formula. This approach uses XMATCH/FREQUENCY, it is just a modification from the answer I provided to your previous question: In Excel: List items, count and sort by descending order, combine with validate menu for search period, just adapted to specific needs of this question (check the answer for additional information):

=LET(dates, A2:A400, endDate, MAX(dates), period, SWITCH(D1,
    "24 HOURS", 1, "3 DAYS", 3, "7 DAYS", 7, "2 WEEKS", 14, "1-MONTH", 30,
    "3-MONTHS", 90, "6-MONTHS", 180, "1 YEAR", 360, "2 YEARS", 720,
    "3 YEARS", 1080),startDate, IF(D1="TOTAL", MIN(dates), endDate-period),
  fDates, INT(FILTER(dates, (dates >= startDate) * (dates <= endDate)
    * (MOD(WEEKDAY(dates),7)>1))), fDatesUx, UNIQUE(fDates),
  match, XMATCH(fDates, fDatesUx), freq, FREQUENCY(match, UNIQUE(match)),
  SORT(CHOOSE({1,2},fDatesUx, FILTER(freq, freq<>0)),1)
)

If you prefer to use Excel Racon's functions (RAnge-based CONditional functions), then you can try the following, which is a similar approach to the formula shared in the question, you get the same result of the below-shared screenshot:

=LET(dates, A2:A400, endDate, MAX(dates), period, SWITCH(D1,
    "24 HOURS", 1, "3 DAYS", 3, "7 DAYS", 7, "2 WEEKS", 14, "1-MONTH", 30,
    "3-MONTHS", 90, "6-MONTHS", 180, "1 YEAR", 360, "2 YEARS", 720,
    "3 YEARS", 1080), startDate, IF(D1="TOTAL", MIN(dates), endDate-period),
  fDates, FILTER(dates, (dates >= startDate) * (dates <= endDate)
    * (MOD(WEEKDAY(dates),7)>1)), fDatesUx, UNIQUE(INT(fDates)),
  counts, COUNTIFS(dates, ">="&fDatesUx, dates, "<"&(fDatesUx+1)),
  SORT(CHOOSE({1,2},fDatesUx, counts),1)
)

Here is the sample output

Explanation

The solution looks backward, based on the input parameter on cell D1, but it can be customized differently based on your preferences. In the endDate, in order to get data I use the maximum data from the input dataset, but it can be customized differently, for example, you can use TODAY() but then the result can change once the day changes.

The main idea is to filter the dates based on ranges and weekdays. The condition for weekdays is the following:

MOD(WEEKDAY(dates),7)>1 -> TRUE

Once we have fDates. We cannot use Excel Racon's function, because they require ranges, and since we applied it to the filter result INT is not a range anymore and instead an array. To do a count-if we can use instead XMATCH/FREQUENCY pattern explained in the answer to the previous question.

Answer 2

Just another approach at the definition of 24 hours or 1 month.

I got a bit confused by you starting the filter at a condition named 24 hours while refering to qty's a day. This would imply that if you set the filter at 22:13 today it would have to filter anything between 22:13 yesterday and now. Also 1 month is not equal to 30 days, but EDATE() allows you to calculate in real months. In that case the following solution could be usefull:

=LET(d,HubLog[Date],
     s,SWITCH(D1,
                 "2 4 H O U R S",   NOW()-1,
                 "3  D A Y S",      NOW()-3,
                 "7  D A Y S",      NOW()-7,
                 "2  W E E K S",    NOW()-14,
                 "1  M O N T H",    EDATE(NOW(),-1),
                 "3  M O N T H S",  EDATE(NOW(),-3),
                 "6  M O N T H S",  EDATE(NOW(),-6),
                 "1  Y E A R",      EDATE(NOW(),-12),
                 "2  Y E A R S",    EDATE(NOW(),-24),
                 "3  Y E A R S",    EDATE(NOW(),-36),
                 "A L L",           INT(MIN(d))),
     e,NOW(),
             days,              SEQUENCE(1+e-s,,s),
             weekdays,          FILTER(days,MOD(INT(days),7)>1),
             hourcorrection,    VSTACK(TAKE(weekdays,1),TAKE(INT(weekdays),1-COUNTA(weekdays))),
             qty,               COUNTIFS(HubLog[Date],">="&hourcorrection,HubLog[Date],"<"&hourcorrection+1),
CHOOSE({1,2},INT(hourcorrection),qty))

Or to filter out days without values:

=LET(d,HubLog[Date],
     s,SWITCH(D1,
                 "2 4 H O U R S",   NOW()-1,
                 "3  D A Y S",      NOW()-3,
                 "7  D A Y S",      NOW()-7,
                 "2  W E E K S",    NOW()-14,
                 "1  M O N T H",    EDATE(NOW(),-1),
                 "3  M O N T H S",  EDATE(NOW(),-3),
                 "6  M O N T H S",  EDATE(NOW(),-6),
                 "1  Y E A R",      EDATE(NOW(),-12),
                 "2  Y E A R S",    EDATE(NOW(),-24),
                 "3  Y E A R S",    EDATE(NOW(),-36),                 "A L L",           INT(MIN(d))),
     e,NOW(),
             days,              SEQUENCE(1+e-s,,s),
             weekdays,          FILTER(days,MOD(INT(days),7)>1),
             hourcorrection,    VSTACK(TAKE(weekdays,1),TAKE(INT(weekdays),1-COUNTA(weekdays))),
             qty,               COUNTIFS(HubLog[Date],">="&hourcorrection,HubLog[Date],"<"&hourcorrection+1),
res,CHOOSE({1,2},INT(hourcorrection),qty),
FILTER(res,qty<>0))