Help with Count If formula on unique values only

VicR

I am trying to add a count to my sheet summary which gives me the number of contracts which are a certain status within my sheet eg. pending, signed & returned etc but each contract can be listed a number of times within the sheet and I only want the contract to count once in my summary.

For Example:

These entries are all for the same contract, 597, but in my count it shows as 25 entries not just 1.

I have seen a number of threads on similar issues and tried a whole combination of COUNTIFS, ANDS, COLLECT, DISTINCT formula combinations but each one has returned an error. Can anyone help me with the correct functions / formula for this?

Thanks

Vic

Ramzi K

@VicR

The combination of functions you're looking for are probably this: =COUNT(DISTINCT(COLLECT(

In your example, this would get you a count of 1 instead of 25:

=COUNT(DISTINCT(COLLECT([Contract Status]:[Contract Status], [Contract Status]:[Contract Status], [Contract Status]@row, [Coupa Contract Number]:[Coupa Contract Number], [Coupa Contract Number]@row)))

I hope this helps.

Cheers,

Ramzi

VicR

@Ramzi K thank you!

how about if i were to use this in a sheet summary on the whole sheet? i have tried the formula as above but i get #unparseable any ideas on how it needs to be changed to work on the whole sheet with different contract numbers?

Ramzi K

@VicR

Can you copy/paste your formula so I can see it?

Cheers,

Ramzi