Formula to return count of duplicate dates based on multiple criteria

Question

In Google Sheets, I have a column of dates in A and checkboxes in column B (Redeemed).

1. I want a formula to count, for each unique date, how many times it appears in the column where the following two conditions are also met:

   1. The date is in the same month as a lookup date in D2
   2. The checkbox in the date's row is TRUE (ticked)

2. I want to use that count to calculate:

   1. In E3, the sum of the count
   2. In E4, the number of times the count includes the number 2
   3. In E5, the number of times it includes the number 3
   4. In E6, the number of times it includes a number >=3

Example Showing Results E3:E6

In the example above,

1. E4=11 because there are 11 dates in the month of October, 2023 (lookup month) that also have their "Redeemed" checkbox ticked.
2. E5=1 because there is only one date, 10/2/2023, that appears exactly twice, is in the lookup month, and has the "Redeemed" checkbox ticked.
3. E6=1, only 10/5/2023 appears exactly 3 times and meets the other conditions
4. E7=2, 10/5/2023 and 10/10/2023 appear 3 or more times and meet the other conditions.

Answer 1

Demo Google Spreadsheet

Includes the following tabs:

1.  ReadMe
2.  Query_Approach
3.  Arrayformula_Approach
4.  Dynamic_Months_Approach

Query Approach in D4

• Demo Spreadsheet: Query_Approach

=LET(
   nums, QUERY(
     A2:B, "Select COUNT(A) where B=TRUE
     and MONTH(A)+1 contains '"&MONTH(E2)&"'
     and YEAR(A) contains '"&YEAR(E2)&"'
     Group by A"),
 { "TTL Redeemed", SUM(nums);
   "2 Redeemed",  COUNTIF(nums,2);
   "3 Redeemed",  COUNTIF(nums,3);
   "3+ Redeemed", COUNTIF(nums,">=3") })

Arrayformula Approach in D4

• Demo Spreadsheet: Arrayformula_Approach

=LET(dates,A2:A, lkup,D2, 
   nums,MAP(UNIQUE(dates), LAMBDA(d,
     ARRAYFORMULA(
       SUM((d-DAY(d)=lkup-DAY(lkup))*
         (dates=d)*(OFFSET(dates,0,1)))))),
 { "TTL Redeemed", SUM(nums);
   "2 Redeemed",  COUNTIF(nums,2);
   "3 Redeemed",  COUNTIF(nums,3);
   "3+ Redeemed", COUNTIF(nums,">=3") })

Dynamically Generate Lookup Months

• Demo Spreadsheet: Dynamic_Months_Approach

=LET(rng,A3:A, 
  uniq, UNIQUE(TOCOL(rng,1)),
  months, SORT(ARRAYFORMULA(UNIQUE(
     EOMONTH(rng*OFFSET(rng,0,1),-1))+1)), 
  calc, LAMBDA(c, MAP(uniq, LAMBDA(u,
     ARRAYFORMULA(SUM((EOMONTH(u, -1)+1=c)*
          (rng=u)*(OFFSET(rng, 0, 1))))))),
  BYROW(TOCOL(months,3), LAMBDA(m, LET(
     count,calc(m), 
     IFERROR(ARRAYFORMULA(1/(1/
      { m, SUM(count),  COUNTIF(count, 2), 
        COUNTIF(count, 3),
        COUNTIF(count, ">=3") })), "-")))))

• This approach returns the results in a row instead of a column.
• It will automatically list all months for which there is a redemption, and adds any new months as redemptions are added in A2:A.
• Months are sorted ascending but that can be switched by modifying the SORT in the variable months

---

Screenshots

_{Lookup Date   ( custom date format; day is irrelevant )}

_{Query Approach}

_{Arrayformula Approach}

_{Dynamically Generate Lookup Months}