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

[willow1985]

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

 

[DanteAmor]

Try this array formula:

varios 10feb2020.xlsm
	A	B	C	D	E
1
2		Task	STATUS	Unique Completed	3
3		54	Completed
4		54	Completed
5		53	In progress
6		50	In progress
7		49	Completed
8		72	In progress
9		72	In progress
10		72	In progress
11		29	Completed
12		45	Completed
sheet

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(--IF($C$3:$C$10000<>"",IF($C$3:$C$10000<>"Completed",MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

 

[willow1985]

Is there a way to modify this formula?

=SUMPRODUCT(1/COUNTIF(B3:B10000,B3:B10000))

This formula counts all unique values, but I want to add conditions on it.

This project requires having a quite a few conditions that I will be having to add going forward

1) IF column C is not "Complete"
2) IF Column F has "BLUE" OR "500"
3) IF Column F has "RED" OR "777"
4) IF Column E is not "Company Name"
5) and ALL remaining minus 2,3 and 4

Along with keeping the condition to count only if it is "Not Blank"

So it will be getting complicated and I was hoping there was a simpler way of achieving a count of unique values with additional conditions...

 

[willow1985]

I put the data in a table now, so the formula is:

=SUMPRODUCT(1/COUNTIF(ML[Task '#],ML[Task '#]))

Don't know if this will help though...

 

[willow1985]

Ok I made a dummy sheet to view.

On the Data tab highlighted in yellow are the formulas I require. They all have the same basis: counting unique values/not duplicates (tasks) with various conditions.

I figured if I had one working template I could figure out the rest. Either way here is the dummy spreadsheet with all sensitive information removed:

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[DanteAmor]

Ok I made a dummy sheet to view.

But there is no example explained with the expected result.
In fact, the result a priori is 0.
There are no records that meet all conditions.

2) IF Column F has "BLUE" OR "500"
3) IF Column F has "RED" OR "777"

You can explain that condition.
Or rather it is:
2) IF Column F has "BLUE" OR "500" OR "RED" OR "777"

IF Column E is not "Company Name"

What does that mean?

and ALL remaining minus 2,3 and 4

What does that mean?

 

[willow1985]

(Reference Spreadsheet)

Condition	Formula Result	Manual Method
Total Tasks Open (not complete)	5	filter Status to not include Completed
All tasks for "BLUE" OR "500"	17	filter Model to select BLUE and 500
All tasks for "RED" OR "777"	3	filter Model to select RED and 777
All Tasks for Company 5	3	filter Manufacturer to select Company 5
Remaining Tasks (not BLUE, 500, RED, 777 or Company 5)	16	Remaining data minus the above 3 highlighted results

Hope this helps.

 

[DanteAmor]

(Reference Spreadsheet)

Condition	Formula Result	Manual Method
Total Tasks Open (not complete)	5	filter Status to not include Completed
All tasks for "BLUE" OR "500"	17	filter Model to select BLUE and 500
All tasks for "RED" OR "777"	3	filter Model to select RED and 777
All Tasks for Company 5	3	filter Manufacturer to select Company 5
Remaining Tasks (not BLUE, 500, RED, 777 or Company 5)	16	Remaining data minus the above 3 highlighted results

Hope this helps.

Sorry but I don't understand, do you want a formula to include all those conditions or do you want 5 formulas?

 

[DanteAmor]

Try this:

Cell Array Formula V2 {=SUMPRODUCT(--IF($B$3:$B$10000<>"",IF($D$3:$D$10000<>"Completed",MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))} W2 {=SUMPRODUCT(--IF($B$3:$B$10000<>"",IF((F3:F10000="BLUE")+(F3:F10000=500),MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))} X2 {=SUMPRODUCT(--IF($B$3:$B$10000<>"",IF((F3:F10000="RED")+(F3:F10000=777),MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))} Y2 {=SUMPRODUCT(--IF($B$3:$B$10000<>"",IF($E$3:$E$10000="Company 5",MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))} Z2 {=SUMPRODUCT(--IF($B$3:$B$10000<>"",IF((F3:F10000<>"BLUE")*(F3:F10000<>500)*(F3:F10000<>"RED")*(F3:F10000<>777)*($E$3:$E$10000<>"Company 5"),MATCH($B$3:$B$10000,$B$3:$B$10000,0)=ROW($B$3:$B$10000)-ROW($B$3)+1)))}

Dropbox - File Deleted - Simplify your life

www.dropbox.com

 

[willow1985]

5 Formulas, was looking for one and then I was going to modify it for the other conditions but it seems more complicated than I thought.

Your link is not working, I get an error.

I put your formulas in and I am getting proper results.

Thank you so much

 

[willow1985]

I was hoping there would be simpler formulas for this put I suppose not. Thank you again for all of these!