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

 

[DataBlake]

would you accept a UDF solution?

 

[willow1985]

I have never created a UDF but would definitely be interested

Always happy to learn something new

 

[Eric W]

Here is another set of formulas, which I believe are a little simpler due to using some tables.

Book1
	A	B	C	D	E	F	G	H	I	J
1	Control	Count		Column	Value1	Value2				Completed
2	Total Tasks Open (not complete)	4								In Progress
3	Total number of Tasks	28								On Hold
4	Total remaining estimated hours	1								Investigating
5	All tasks for "BLUE" OR "500"	12		Model	BLUE	500
6	All tasks for "RED" OR "777"	3		Model	RED	777
7	All Tasks for Company 5	1		Manufacturer	Company 5
8	Remaining Tasks (not BLUE, 500, RED, 777 or Company 5)	13		Model	BLUE	500	RED	777
9				Manufacturer	Company 5
Data

Cell Formulas
Range	Formula
B2	B2	{=SUM(SIGN(FREQUENCY(IF(('Manual Log'!D3:D100<>"completed")*('Manual Log'!D3:D100<>""),'Manual Log'!B3:B100),'Manual Log'!B3:B100)))}
B3	B3	=SUMPRODUCT(1/COUNTIF(ML[Task '#],ML[Task '#]))
B4	B4	=SUMIF('Manual Log'!$D3:$D1000000,"<>*"&J1&"*""",'Manual Log'!$P3:$P1000000)
B5:B7	B5	{=SUM(SIGN(FREQUENCY(IF(ISNUMBER(MATCH(INDEX('Manual Log'!A$3:J$100,0,MATCH(D5,'Manual Log'!$A$2:$J$2,0)),E5:F5,0)),'Manual Log'!B$3:B$100),'Manual Log'!B$3:B$100)))}
B8	B8	{=SUM(SIGN(FREQUENCY(IF(ISNA(MATCH(INDEX('Manual Log'!A$3:J$100,0,MATCH(D8,'Manual Log'!$A$2:$J$2,0)),E8:H8,0))*ISNA(MATCH(INDEX('Manual Log'!A$3:J$100,0,MATCH(D9,'Manual Log'!$A$2:$J$2,0)),E9:H9,0)),'Manual Log'!B$3:B$100),'Manual Log'!B$3:B$100)))}
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.

My original formula had a typo in it, which is why you got invalid results. I've fixed it in B2.

The B5 formula looks for the column heading from the Manual Log sheet, then looks for the values from E5:F5 to decide which to include. Therefore the same formula can be used in B6:B7. The B8 formula is a bit trickier. It uses the same logic, but in reverse and with 2 conditions.

Also, these formulas could be a bit shorter if you use table references. You said you want to potentially include rows beyond the end of the current data, but if you add data to a table, the table expands. Or are you adding data another way?

Anyway, another option to consider. Let me know if you need it tweaked.

 

[willow1985]

I see, using INDEX, MATCH...smart!

One quick additional question based on the spreadsheet I uploaded:

The last count of the remaining tasks "BLUE", "500", "RED" etc, I am trying to SUM of column T for those left overs so What I did is list all those items in F1 to F4 and in cell F5 Company 5.

I entered this formula: =SUMIFS('Manual Log'!$T:$T,'Manual Log'!$F:$F,"<>F1:F4",'Manual Log'!$E:$E,"<>F5")

However I get a larger total than I should (1913 instead of 1213). Since you guys are already playing in the spreadsheet, any idea what I entered wrong?

Thank you

Carla

 

[DanteAmor]

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

Thank you so much

Glad we could help & thanks for the feedback

 

[willow1985]

As per the rules I should start a new post for the Sum formula.

Thank you everyone!

 

[Eric W]

There are a couple issues with that formula. First, when looking to match a value in F5, you need to code it like "<>"&F5 not "<>F5", otherwise it looks for the text "F5" and not the contents of the F5 cell. Second, the <>F1:F4 part doesn't work well that way, you need to split all 4 cells out individually. So the whole formula is:

=SUMIFS('Manual Log'!$T:$T,'Manual Log'!$F:$F,"<>"&F1,'Manual Log'!$F:$F,"<>"&F2,'Manual Log'!$F:$F,"<>"&F3,'Manual Log'!$F:$F,"<>"&F4,'Manual Log'!$E:$E,"<>"&F5)

Another option is to use SUMPRODUCT instead, which can use MATCH. (SUMIFS can't.)

=SUMPRODUCT('Manual Log'!T3:T1000,--('Manual Log'!E3:E1000<>F5),--ISERROR(MATCH('Manual Log'!F3:F1000,F1:F4,0)))

The first condition (red) is the values, the second (blue) checks for the F5 value, and the third (green) checks each value in 'Manual Log'!F3:F1000 to see if it's in F1:F4, and if not (IFERROR) gives a good condition.

In using SUMPRODUCT by the way, you should avoid whole column references. That's OK in SUMIFS since SUMIFS is "aware" of the last column, but SUMPRODUCT is not, so it has to check over a million rows, which is slow.

I already had this written, so I'll post it here.

 

[willow1985]

Thank you Eric W, I posted the question on Tired and cant see the error in my COUNTIFS AND SUMIFS formula
which also includes the same question for a countifs formula as well.

I will try your formula out right now.

Thank you