SOLVED

Count number of occurrences only once

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3172633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ECount%20number%20of%20occurrences%20only%20once%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3172633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EGood%20afternoon%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EI%20have%20a%20column%20where%20values%20are%20repeated%20many%20times.%20I%20need%20to%20count%20the%20occurrences%20of%20each%20value%20in%20another%20column%2C%20so%20I%20was%20thinking%20to%20use%20COUNTIF%2C%20but%20if%20I%20extend%20COUNTIF%20over%20the%20entire%20column%2C%20the%20occurrences%20of%20each%20value%20will%20be%20counted%20each%20time%20the%20value%20appears.%20I%20need%20each%20value's%20occurrences%20to%20be%20counted%20only%20once%2C%20like%20shown%20in%20the%20example%20screenshot%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348675i6093E3B27ADC77FC%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22Ber93_0-1645011885830.png%22%20alt%3D%22%5C%26quot%3BBer93_0-1645011885830.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3172633%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

Good afternoon

 

I have a column where values are repeated many times. I need to count the occurrences of each value in another column, so I was thinking to use COUNTIF, but if I extend COUNTIF over the entire column, the occurrences of each value will be counted each time the value appears. I need each value's occurrences to be counted only once, like shown in the example screenshot

Ber93_0-1645011885830.png

 

2 Replies
best response confirmed by Ber93 (Occasional Contributor)
Solution

@Ber93 

=IF(COUNTIF($CL$1:CL2,CL2)>1,"",COUNTIF($CL$1:$CL$1000,CL2))

Maybe with this formula.

Works perfectly! Thank you