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

willow1985

Active Member
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
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
Joined
Aug 18, 2015
Messages
9,167
How about:

=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
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
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
Joined
May 28, 2005
Messages
42,706
Office Version
365
Platform
Windows
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:

1581165688492.png


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
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
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

1581351463737.png
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,698
Office Version
2007
Platform
Windows
Hi Carla:

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

Libro4
ABCDE
1TaskSTATUSUnique Count3
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
Joined
Aug 18, 2015
Messages
9,167
I'll let the others adapt their formulas as needed. Although it doesn't sound as if you can use Peter's.

Book1
BCDE
3Task #StatusUnique Count:3
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
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
Hi Carla:

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

Libro4
ABCDE
1TaskSTATUSUnique Count3
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
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
I'll let the others adapt their formulas as needed. Although it doesn't sound as if you can use Peter's.

Book1
BCDE
3Task #StatusUnique Count:3
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
Joined
Jul 24, 2019
Messages
424
Office Version
2019
Platform
Windows
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.
 

Forum statistics

Threads
1,085,757
Messages
5,385,706
Members
401,967
Latest member
Sullivag2

Some videos you may like

This Week's Hot Topics

Top