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

willow1985

Well-known Member
Joined
Jul 24, 2019
Messages
871
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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.
 
Upvote 0
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))
 
Upvote 0
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")))
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,048
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top