Excel formula count unique values with multiple criteria
- Thread starter AV114
- Start date
AlanSidman
Well-Known Member
=COUNTIF($B$2:$B$41,E4)
pal7mentor
New Member
Use a combination of the IF, SUM, FREQUENCY, MATCH, and LEN functions to do this task:
- Assign a value of 1 to each true condition by using the IF function.
- Add the total by using the SUM function.
- Count the number of unique values by using the FREQUENCY function. The FREQUENCY function ignores text and zero values. For the first occurrence of a specific value, this function returns a number equal to the number of occurrences of that value. For each occurrence of that same value after the first, this function returns a zero.
- Return the position of a text value in a range by using the MATCH function. This value returned is then used as an argument to the FREQUENCY function so that the corresponding text values can be evaluated.
- Find blank cells by using the LEN function. Blank cells have a length of 0.
Last edited by a moderator:
shrivallabha
Excel Ninja
You can try following array formula (to be committed by CTRL+SHIFT+ENTER)
=SUM(--(FREQUENCY(IF(B2:B41=E4,INT(A2:A41),""),INT(A2:A41))>0))
=SUM(--(FREQUENCY(IF(B2:B41=E4,INT(A2:A41),""),INT(A2:A41))>0))