Search code examples
excelexcel-formulaspreadsheetexcel-tables

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


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


Solution

  • 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 sample excel 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.