COUNTIF(DISTINCT

PeterLatcapital

Hello Community, I am attempting to count unique numbers in one column if the code in the other column is "X". =COUNTIF(DISTINCT([Claim Number]:[Claim Number], [Contract Type]:[Contract Type], @cell = "LW"))

Claim numbers are formatted as letter and numbers. Each part work fine separately, but not together.

What am I doing wrong

Cheers,

Peter

Christian G.

Here is a different approch

• Collect all the Claim number rows with contract type "LW",

COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")

• select only the distinct ones,

DISTINCT(...)

• count them.

COUNT(...)

=COUNT(DISTINCT(COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")))

Christian G.

Distinct seems to only take one range as parameter.

Break it in 2

=COUNTIF(DISTINCT([Claim Number]:[Claim Number]), @cell = "LW")+COUNTIF(DISTINCT( [Contract Type]:[Contract Type]), @cell = "LW")

PeterLatcapital

Hi Christian,

First, thank you!

I only need one column to be distinct [claim number] which is the one I want to count, if the other column meets the criteria [contract type] is "LW".

Peter

Christian G.

Here is a different approch

• Collect all the Claim number rows with contract type "LW",

COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")

• select only the distinct ones,

DISTINCT(...)

• count them.

COUNT(...)

=COUNT(DISTINCT(COLLECT([Claim number]:[Claim number], [contract type]:[contract type], "LW")))

PeterLatcapital

Brilliant!!! It worked. Much appreciated. You made my day!