0

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.

1 Answer 1

0
Demo Google Spreadsheet

Includes the following tabs:

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

Query Approach in D4

=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

=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

=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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.