HAS Formula Returning Wrong Results

Pawillia

Hey Community,

I've created a formula to count, collect, and return results based on some criteria. I have also created a report that gives me what I'm looking for. At the report level the results come back correctly, but in my formula to just get a count. It does not.

=COUNT(COLLECT({Intensity}, {TargetDate1}, >-30, {TargetDate1}, <0, {Region}, HAS(@cell, "APAC")))

The target date reference is just a calculation between a start date and end date. What I believe is giving me the incorrect value is the has formula for the region. The above formula is returning 2 as the results. Where as the report created with the similar filters is returning 5 results. So somehow I'm missing 3 results and unsure on what would cause the difference. I do have 3 different regions that could be chosen from the dropdown (Below screenshot).

Filter on report below. [CORRECT AMOUNT RETURN]

Jeff Reisman

@Pawillia

Why not use COUNTIFS instead of COUNT and COLLECT? This will count the number of rows where all three of these conditions are true:

=COUNTIFS({TargetDate1}, >-30, {TargetDate1}, <0, {Region}, HAS(@cell, "APAC"))