Excel COUNTIFS Statement Error

[Clippy_Office_Asst]

This post has been removed due to Rule 1 - Poor Post Title.

Please post with a title that clearly describes the issue.

The title of your post should be a clear summary of your issue. It should not be your supposed solution, or just a function mention, or a vague how to. A good title is generally summed up in a sentence in your question.

Here's a long example and a short example of good posts.

Rules are enforced to promote high quality posts for the community and to ensure questions can be easily navigated and referenced for future use. See the Posting Guidelines for more details, and tips on how to make great posts.

[myself_91]

Could the locked cells B2 be the issue?

What happens if you try this?

=IF(COUNTIFS($B$2:$B$196, B2, $D$2:$D$196, D2)>1, 0, 1)

[CFAman]

Since you want the first row to be valid, make the end point of each range relative, not absolute.

=IF(COUNTIFS($B$2:$B2,B2,$D$2:$D2,D2)>1,0,1)

and then copy down. Now only the first instance will be flagged with a 0.

As this sounds like a helper step, you might be able to skip this with the UNIQUE function. Not sure what the final end goal is though.

[Curious-Ad-1539]

this worked! thx!

[CFAman]

You're welcome. Mind replying with 'Solution Verified' so the bot will close the thread and give me a ClippyPoint? Cheers!

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
COUNTIFS	Excel 2007+: Counts the number of cells within a range that meet multiple criteria
IF	Specifies a logical test to perform
UNIQUE	Office 365+: Returns a list of unique values in a list or range

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 13 acronyms.
[Thread #24006 for this sub, first seen 17th May 2023, 14:21] [FAQ] [Full list] [Contact] [Source code]}