List unique items, count and sort numerically by descending order, and combine with validate menu for search period

Question

I need to apply solution found in this question:
Using single formula to list unique items, count and sort numerically by descending order and then alphabetically for items with same count

This time adding a data validation menu for the search period.

I can do this for single cell but I'm not able to apply it to the solution above.

Formula for search period is this:

=COUNTIFS($B3:$B,"*apple*",$A3:$A,">="&TODAY()- VLOOKUP(
           SUBSTITUTE(D2," ",""),
            {"24HOURS",0;
            "2DAYS",1;
            "3DAYS",4; 
            "7DAYS",7; 
            "2WEEKS",14; 
            "1MONTH",30;
            "3MONTHS",90; 
            "6MONTHS",180; 
            "1YEAR",365;
            "2YEARS",730; 
            "3YEARS",1095;
            "TOTAL",999999},
           2,FALSE))

Formula taken from solution on question above:

=QUERY(B:B,"Select B, count(B) where B matches '^(?!(?:ITEMS|ExcludeB|ExcludeC)$).+' group by B order by count(B) DESC label count(B) ''")

Image to help clarify:

My dummy file:
https://docs.google.com/spreadsheets/d/1iB4BnqhTBVNuCCQ4GnEIu95gbzYb0T9H9A3Pi1W5AZk/edit?usp=sharing

Is such a thing possible? Any pointers on how this can be done? Thank you

I need the listing + counting of the items done by the ```QUERY``` formula on ```D4``` be responsive to the time period (```A3:A```) through the data validation menu on ```D2```. In other words the ```QUERY``` formula should display the listing and counting just for the time period chosen. For example if on the last 24h hours we just had 2 apples, 1 orange and 1 banana, then the ```QUERY``` formula, if I choose 24h on the data validation menu, the ```QUERY``` formula would will list and count just those items and nothing else. The formula on ```G4``` was a piece of formula I was trying to use — Verminous, Jun 15 '22 at 12:23
Basically you want to list and count items between two dates period. You also need to convert drop down selection to dates inside formula. — Harun24hr, Jun 16 '22 at 02:05
Yes between 2 dates. In this case it is always between ```NOW()``` and a previous date which changes according to the time period chosen. If it is 7 days it would be ```NOW()``` minus 7 days. The counting should be sorted. And within items with same count sorted alphabetically. But the ```QUERY``` formula is already doing that. I would like to maintain it. The formula I posted above on ```G4``` cell converts the selection to time. I seem to be unable to find a way to combine the 2 formulas above. — Verminous, Jun 16 '22 at 07:31

P.b · Answer 1 · 2022-06-14T18:29:04.603

2

In Excel (since you tagged it) you can use the following in Office 365:

=LET(a,A2:INDEX(B:B,LOOKUP(2,1/(A:A<>""),ROW(B:B))),
     aa,INDEX(a,,1),
     ab,INDEX(a,,2),
     u,UNIQUE(INDEX(a,,2)),
     c,COUNTIF(ab,u),
     d,COUNTIFS(ab,u,
                aa,">="&TODAY()
                      -VLOOKUP(SUBSTITUTE(D2," ",""),
        {"24HOURS",0;
         "2DAYS",1;
         "3DAYS",4;
         "7DAYS",7;
         "2WEEKS",14;
         "1MONTH",30;
         "3MONTHS",90;
         "6MONTHS",180;
         "1YEAR",365;
         "2YEARS",730;
         "3YEARS",1095;
         "TOTAL",999999},
         2,0)),
SORT(CHOOSE({1,2,3},u,c,d),{2,1,1},{-1,1,1}))

edited Jun 14 '22 at 18:29

answered Jun 14 '22 at 16:46

P.b

8,293
2
10
25

Yes I tagged Excel because I wanted to see how you do it in Excel but my main worksheet is in Google Sheets. And in this case unfortunately Google did not incorporate ```LET()```function into Google Sheets. Thanks anyway! – Verminous Jun 14 '22 at 22:14
1

It's better to not tag both Excel-formula and Google-sheets-formula since they are different, especially in array formula's. – P.b Jun 15 '22 at 05:55
Ok I'll be sure to not tag Excel in the future. Thanks. – Verminous Jun 15 '22 at 06:48
Hey @P.b I just used this now in Excel. So this was useful! Just one problem though. The 3rd column with the count by period is not working. If you want I can place a question for this. – Verminous Oct 28 '22 at 10:18
I no longer have access to the sample file and am unable to reproduce myself – P.b Oct 28 '22 at 14:57
I tested it and it still seems to work. With given data all would be 0 for `3 days`, since the data is old. For `1 year` it counts them. – P.b Oct 29 '22 at 19:04
https://i.stack.imgur.com/FZkzl.jpg judging other comments, you may need to change the range to `A3` – P.b Oct 29 '22 at 19:05
I wasn't precise enough sorry. So basically it works on the example file the same as for you. When I add this to my real-life file, the table is on another worksheet, because I want this formula on the stats worksheet. Also, the table has more columns. I referenced correctly the columns containing the items I want to count by period. Everything is ok (I double-checked) but somehow the count by period is always zero. Even changing the period does not change anything. Maybe this only works when everything is in the same worksheet? – Verminous Oct 29 '22 at 19:23
Changing to A3 does not solve it. – Verminous Oct 29 '22 at 19:29
The worksheet shouldn't matter, as long as you reference it correctly. To know what's going on, another dummy sheet would be preferred – P.b Oct 29 '22 at 19:51
Hey @P.b Here is my question about this: https://stackoverflow.com/questions/74248865/in-excel-list-items-count-and-sort-by-descending-order-combine-with-validate Just posted it now. It has the link to a dummy file. Thanks – Verminous Oct 29 '22 at 21:12

tomf · Accepted Answer · 2022-06-17T07:32:43.373

This should do it.

=QUERY(
  A:B,
  "Select B, count(B) 
   where 
    B matches '^(?!(?:ITEMS|ExcludeB|ExcludeC)$).+' and
    A >= date '"&
     TEXT(
      IFERROR(
       VLOOKUP(
        D2,
        {"2 4 H O U R S",TODAY()-1;
         "3  D A Y S",TODAY()-3;
         "7  D A Y S",TODAY()-7;
         "2  W E E K S",TODAY()-14;
         "1  M O N T H",EDATE(TODAY(),-1);
         "3  M O N T H S",EDATE(TODAY(),-3);
         "6  M O N T H S",EDATE(TODAY(),-6);
         "1  Y E A R",EDATE(TODAY(),-12);
         "2  Y E A R S",EDATE(TODAY(),-24);
         "3  Y E A R S",EDATE(TODAY(),-36)},
        2,FALSE),0),"yyyy-mm-dd")&"'
   group by B
   order by 
    count(B) DESC,
    B asc 
   label count(B) ''")

Using an array

=QUERY(
  {A3:A,E3:E},
  "Select Col2, count(Col2) 
   where 
    Col2 matches '^(?!(?:ITEMS|ExcludeB|ExcludeC)$).+' and
    Col1 >= date '"&
     TEXT(
      IFERROR(
       VLOOKUP(
        G2,
        {"2 4 H O U R S",TODAY()-1;
         "3  D A Y S",TODAY()-3;
         "7  D A Y S",TODAY()-7;
         "2  W E E K S",TODAY()-14;
         "1  M O N T H",EDATE(TODAY(),-1);
         "3  M O N T H S",EDATE(TODAY(),-3);
         "6  M O N T H S",EDATE(TODAY(),-6);
         "1  Y E A R",EDATE(TODAY(),-12);
         "2  Y E A R S",EDATE(TODAY(),-24);
         "3  Y E A R S",EDATE(TODAY(),-36)},
        2,FALSE),0),"yyyy-mm-dd")&"'
   group by Col2
   order by 
    count(Col2) DESC,
    Col2 asc 
   label 
    Col2 '',
    count(Col2) ''")

I commented previously it was not working but it is. The reason why I said it is not is because it wasn't working on my phone. For some reason it does not work using the sheets app. But works on the PC browser. Just one thing though. The word "ITEMS" is not being excluded. I can't figure out why. — Verminous, Jun 17 '22 at 07:05
On my main file I've got column with dates far away from column with items. I tried using this```{A3:A,E3:E}``` but does not work. Is there a way to reference columns distant from eachother in ```QUERY``` ? — Verminous, Jun 17 '22 at 07:25
no prob. Check the post here or on reddit to see how to use an array. You need to switch to using Col1, Col2. — tomf, Jun 17 '22 at 07:33
Ah yes I just did that and worked. Thanks!! Is there a way to remote "ITEMS" without the workaround of starting at ROW number 3? — Verminous, Jun 17 '22 at 07:37
yeah, you've already got it with the `matches` part. Check the ol' demo sheet we're in. — tomf, Jun 17 '22 at 07:40

List unique items, count and sort numerically by descending order, and combine with validate menu for search period

2 Answers2

Linked