# Count unique values (not duplicates) and if an adjacent cell has certain text

#### willow1985

##### Active Member
Hello,

I am looking for a formula that will count unique values (not duplicates) in column B - the data in this column is numbers
as long as the corresponding data in Column C has the text "Completed".

Any help would be greatly appreciated.

Thank you

Carla

#### Eric W

##### MrExcel MVP

=SUM(SIGN(FREQUENCY(IF(C2:C20="completed",B2:B20),B2:B20)))

confirmed with Control+Shift+Enter. (If you have Excel 365 with the latest updates, you may not need the CSE. But then if you do, you could use a COUNT(UNIQUE(FILTER formula too.)

This does require the B values to be numbers.

#### DanteAmor

##### Well-known Member
Try this

varios 06feb2020.xlsm
ABCD
2VALUESTATUSUnique Completed
31Completed3
41Completed
52
62
72
83Completed
94
105Completed
115Completed
Hoja1
Cell Formulas
RangeFormula
D3D3=SUMPRODUCT((C3:C11="Completed")*(MATCH(\$B\$3:\$B\$11,\$B\$3:\$B\$11,0)=ROW(\$B\$3:\$B\$11)-2))

#### Peter_SSs

##### MrExcel MVP, Moderator
Any help would be greatly appreciated.
Hi Carla, help could be tailored better if we knew what Excel version(s) & operating system(s) you are using. You can show that automatically every post by updating your Account details:

Without that information, I have no idea whether it was actually worth suggesting this way for you.

AA_MrExcel 20 02 08.xlsm
BCD
1VALUESTATUSUnique Completed
21Completed3
31Completed
42
52
62
73Completed
84
95Completed
105Completed
11
Count Unique
Cell Formulas
RangeFormula
D2D2=COUNT(UNIQUE(FILTER(B2:B20,C2:C20="Completed")))

#### willow1985

##### Active Member
I apologize for the late response, posted my question at the end of the day on Friday.

I posed my question incorrectly (was a long day and I apologize),

I meant to count unique values among duplicates in column B as long as the corresponding data in Column C does NOT have the text "Completed".

Either way I tried all of your formulas suggested with the modification of "<>*Completed*" but I get errors.

This formula I did not structure correctly, so I am missing something:

=SUMPRODUCT(('Manual Log'!C3:C100000,"<>*Completed*")*(MATCH('Manual Log'!\$B\$3:\$B\$100000,'Manual Log'!\$B\$3:\$B\$100000,0)=ROW('Manual Log'!\$B\$3:\$B\$100000)-2))

This formula I get a "function isn't valid error" probably due to my version like Peter suggested:

=COUNT(UNIQUE(FILTER('Manual Log'!B3:B100000,'Manual Log'!C3:C100000,"<>*Completed*")))

and last I get the #VALUE error with the one below:

{=SUM(SIGN(FREQUENCY(IF('Manual Log'!C3:C100000,"<>*Completed*",'Manual Log'!B3:B100000),'Manual Log'!B3:B100000)))}

Peter_SSs, I apologize, I did not know I could put my version of excel in the profile/didn't notice that section. I am using version 2019 and have updated my profile.
I cannot use XL2BB as my computer is a work computer and has security locks implemented by our IT department that will not allow me to install any add on's.
I hope screen shots are ok with everyone, I apologize for any inconvenience and appreciate all of your help

#### DanteAmor

##### Well-known Member
Hi Carla:

You were close, you shouldn't put "<>*Completed*" , just <>, try this:

Libro4
ABCDE
254Completed
354Completed
453In progress
550In progress
649Completed
772In progress
872In progress
972In progress
1029Completed
1145Completed
Sheet
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((C3:C11<>"Completed")*(MATCH(\$B\$3:\$B\$11,\$B\$3:\$B\$11,0)=ROW(\$B\$3:\$B\$11)-2))

#### Eric W

##### MrExcel MVP
I'll let the others adapt their formulas as needed. Although it doesn't sound as if you can use Peter's.

Book1
BCDE
454Completed3
554Completed
653In Progress
750In Progress
849Completed
972In Progress
1072In Progress
1172In Progress
1229Completed
1345Completed
Sheet3
Cell Formulas
RangeFormula
E3E3{=SUM(SIGN(FREQUENCY(IF(C4:C13<>"completed",B4:B13),B3:B13)))}
E4E4{=SUM(SIGN(FREQUENCY(IF(ISERROR(SEARCH("completed",C4:C13)),B4:B13),B3:B13)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

If you just have the word "Completed" (case not mattering), you can just change the = to <>. If you have additional text, like "Completed on 2/12", then use the second version.

#### willow1985

##### Active Member
Hi Carla:

You were close, you shouldn't put "<>*Completed*" , just <>, try this:

Libro4
ABCDE
254Completed
354Completed
453In progress
550In progress
649Completed
772In progress
872In progress
972In progress
1029Completed
1145Completed
Sheet
Cell Formulas
RangeFormula
E1E1=SUMPRODUCT((C3:C11<>"Completed")*(MATCH(\$B\$3:\$B\$11,\$B\$3:\$B\$11,0)=ROW(\$B\$3:\$B\$11)-2))
Hi Dante,

Your formula works but as soon as I modify it to do a larger range I get an #N/A error.

Here is how I need to modify the formula on my end:

=SUMPRODUCT(('Manual Log'!C3:C10000<>"Completed")*(MATCH('Manual Log'!\$B\$3:\$B\$10000,'Manual Log'!\$B\$3:\$B\$10000,0)=ROW('Manual Log'!\$B\$3:\$B\$10000)-2))

How can it be modified without causing the #N/A error?

#### willow1985

##### Active Member
I'll let the others adapt their formulas as needed. Although it doesn't sound as if you can use Peter's.

Book1
BCDE
454Completed3
554Completed
653In Progress
750In Progress
849Completed
972In Progress
1072In Progress
1172In Progress
1229Completed
1345Completed
Sheet3
Cell Formulas
RangeFormula
E3E3{=SUM(SIGN(FREQUENCY(IF(C4:C13<>"completed",B4:B13),B3:B13)))}
E4E4{=SUM(SIGN(FREQUENCY(IF(ISERROR(SEARCH("completed",C4:C13)),B4:B13),B3:B13)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

If you just have the word "Completed" (case not mattering), you can just change the = to <>. If you have additional text, like "Completed on 2/12", then use the second version.
Hi Eric,

I am getting a result of 5 with yours when trying it on the test data (screen shot data).

When I modify it to my ranges (C3:C10000 and B3:B1000) I still get a result of 5 when it should be 3

#### willow1985

##### Active Member
C3 and B3 should be where the data starts correct? and I am unsure where the data will end/ there will be some blanks that it should not count.