Is it possible to add Dynamic subtotal in a google sheet without app script?

Question

Please look at the google sheet below. In the first column it consists serial numbers, 2nd column contains company name. 3rd column contains name of the assignee to which the company is assigned and 4th column contains the assigned date. I want to filter those input with the criteria of from date and to date and the filtration of Assignees. as in the below google sheet picture.

I want to get the result as below picture. in the first column contains the month and year of the companies which included in the dates which are selected as in the first sheet.

Answer 1

You are making this complicated by mixing counts and data (serial numbers) in the same column. Presumably there could be additional months for an assignee and it isn't clear if that would be an additional subtotal section or included in a single section for each assignee. Filtering the data is one piece and getting monthly counts is another.

• EOMONTH(date, -1)+1 was used to convert all dates to the first of the month, and then they the column is formatted as dates "mmm yyyy".
• This seems preferable to formatting them within the formula which will convert them to text strings TEXT(date, "mmm yyyy") that won't sort properly and can't be used in other calculations if needed.

Note: Your date filters use individual dates (rather than whole months) however your aggregated counts are month-based. You may wish to make that consistent.

1.  Filtering

   1a.  Using FILTER

={"Month", A1:D1;
  SORT(FILTER(
    {EOMONTH(D2:D, -1)+1, A2:D}, 
    D2:D>=F2, D2:D<=G2, 
    C2:C=IF(H2="All", C2:C, H2)),1,5)}

EOMONTH  FILTER  IF  SORT

   1b.  Using QUERY

=QUERY({{"Month"; INDEX(EOMONTH(D2:D,-1)+1)}, A:D},
    "WHERE Col5 is not Null AND
     Col5 >= date '"&TEXT(F2, "yyyy-mm-dd")&"' AND
     Col5 <= date '"&TEXT(G2, "yyyy-mm-dd")&"' AND
     Col4 = "&IF(H2="All", "Col4", "'"&H2&"'")&
     " Order by Col5", 1)

EOMONTH  IF  INDEX  QUERY  TEXT

2.  Counting (Aggregate)

   2a.  Using FILTER

=INDEX(LET(
   line,IMAGE("https://i.sstatic.net/FRd1JoVo.png",4,3,99),
   months,EOMONTH(D2:D,-1)+1,
   arr,SORT(
     UNIQUE(
       FILTER(
         {months, COUNTIF(months,months)}, 
         D2:D>=F2, D2:D<=G2, 
         C2:C=IF(H2="All", C2:C, H2)))),
   {"Month", "Count"; arr;"", line;"", SUM(INDEX(arr,,2))}))

COUNTIF  EOMONTH  FILTER  IF  IMAGE  INDEX  LET  SORT  SUM  UNIQUE

   2b.  Using QUERY

=INDEX(LET(
   line,IMAGE("https://i.sstatic.net/FRd1JoVo.png",4,3,99),
   arr,QUERY({{"Month"; EOMONTH(D2:D,-1)+1}, C:D},
     "Select Col1, COUNT(Col1) Where Col3 is not Null AND
      Col3 >= date '"&TEXT(F2, "yyyy-mm-dd")&"' AND
      Col3 <= date '"&TEXT(G2, "yyyy-mm-dd")&"' AND
      Col2 = "&IF(H2="All", "Col2", "'"&H2&"'")&"
      Group by Col1 Label Col1 'Month', Count(Col1) 'Count'",1),
   {arr; "", line;"",SUM(INDEX(arr,,2))}))

COUNT  EOMONTH  IF  IMAGE  INDEX  LET  QUERY  SUM  TEXT