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.

Here is a sample data:

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:

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.