# Formula to return count of duplicate dates based on multiple criteria

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

##### Demo Google Spreadsheet

Includes the following tabs:

## 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