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

3
  • Will this run if I add a new month in F2?
    – Elaine
    Oct 9, 2023 at 4:37
  • "Will this run if I add a new month in F2" The original formulas are not affected by anything in column F they look to D2 for the lookup month. Not sure if that makes the answer "yes" or "no."
    – Blindspots
    Oct 9, 2023 at 21:18
  • I added a demo spreadsheet that ANYONE can edit anonymously. You may wish to save a copy (or two) to your own Google Drive before someone messes it up or the link expires. Also, I added an additional version (Dynamic_Months_Approach) that automatically generates the lookup months and results based on your redemptions in columns A & B. I transposed the layout for that one so the array grows downwards rather than rightwards.
    – Blindspots
    Oct 9, 2023 at 21:21

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.