Number of unique values in a range

%3CLINGO-SUB%20id%3D%22lingo-sub-1729099%22%20slang%3D%22en-US%22%3ENumber%20of%20unique%20values%20in%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729099%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20now%20the%20number%20of%20unique%20values%20in%20a%20range%20(S9%3AU56)%3CSPAN%3E%26nbsp%3Bwhere%20column%20M%20of%20the%20corresponding%20rows%20contains%20a%20certain%20value%20(M9%3AM56%3D%22GVA%22)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20use%20this%20formula%20%3DIF(M9%3AM56%3D%22GVA%22%3BSUM(1%2F(COUNTIF(S9%3AU56%3BS9%3AU56)))-1%3B0)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EThis%20results%20in%20a%20%23SPILL%20Error%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1729099%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1729420%22%20slang%3D%22de-DE%22%3ESubject%3A%20Number%20of%20unique%20values%20in%20a%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1729420%22%20slang%3D%22de-DE%22%3E%23SPILL!%20errors%20in%20Excel%3CBR%20%2F%3E%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2F-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fsupport.microsoft.com%2Fen-gb%2Foffice%2F-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad4023%3Fui%3Den-us%26amp%3Brs%3Den-gb%26amp%3Bad%3Dgb%3C%2FA%3E%3CBR%20%2F%3E%3CBR%20%2F%3EJust%20only%20for%20info%2C%20maybe%20will%20help.%3CBR%20%2F%3E%3CBR%20%2F%3ENikolino%20%3CBR%20%2F%3E%20I%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FLINGO-BODY%3E
Highlighted
Occasional Visitor

I want to now the number of unique values in a range (S9:U56) where column M of the corresponding rows contains a certain value (M9:M56="GVA")

I use this formula =IF(M9:M56="GVA";SUM(1/(COUNTIF(S9:U56;S9:U56)))-1;0)

This results in a #SPILL Error

1 Reply
Highlighted
#SPILL! errors in Excel
https://support.microsoft.com/en-gb/office/-spill-errors-in-excel-ffe0f555-b479-4a17-a6e2-ef9cc9ad40...

Just only for info, maybe will help.

Nikolino
I know I don't know anything (Socrates)