Using OR for different ranges

kioshi43

Hello,

I have a formula I'm trying to improve:

=COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {End Date}, >=DATE(2025, 8, 15)))) + COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {Flag}, 1)))

The issue I'm running into with the above formula is that if a location has both - the flag checked and the end date is after August 8th, then it is counted twice. So I want to fold the criteria into a single formula so that I can get a true distinct count. However I keep running into an error when I try to incorporate different ranges within an OR function and only seem to have success when I'm using it just for the criteria within a range (e.g. the {Category}, OR(@cell = "Category 1", @cell = "Category 2").

Anyone have an idea on how I could modify the formula so that it would collect and count the distinct sites when the Flag is checked or the End Date is after August 15th?

KPH

Could you subtract the rows that are included in both counts? OR plus OR minus AND

=COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {End Date}, >=DATE(2025, 8, 15)))) + COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {Flag}, 1)))

-

COUNT(DISTINCT(COLLECT({Location}, {District}, [Primary Column]@row, {Order}, 1, {Health}, "red", {Category}, OR(@cell = "Category 1", @cell = "Category 2"), {Flag}, 1,{End Date}, >=DATE(2025, 8, 15))))