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:
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
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)
)
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.