Count unique values based on multiple conditions

[RoxyS]

Hello,

I am trying to figure out how to count unique values in column C (session) based on 3 conditions.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Course[/TD]
[TD]Session[/TD]
[TD]Status[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]John [/TD]
[TD]Writing Skills[/TD]
[TD]Writing Skills - May 9[/TD]
[TD]Completed[/TD]
[TD]09/05/2018[/TD]
[/TR]
[TR]
[TD]Kelly[/TD]
[TD]Writing Skills[/TD]
[TD]Writing Skills - May 9[/TD]
[TD]Completed[/TD]
[TD]09/05/2018[/TD]
[/TR]
[TR]
[TD]Steve[/TD]
[TD]Writing Skills[/TD]
[TD]Writing Skills - Nov 11[/TD]
[TD]Incomplete[/TD]
[TD]01/11/2018[/TD]
[/TR]
[TR]
[TD]Stacey[/TD]
[TD]Writing Skills[/TD]
[TD]Writing Skills - Nov 11[/TD]
[TD]Completed[/TD]
[TD]01/11/2018[/TD]
[/TR]
[TR]
[TD]Kate[/TD]
[TD]Writing Skills[/TD]
[TD]Writing Skills - Nov 11[/TD]
[TD]Incomplete[/TD]
[TD]01/11/2018[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Presentation Skills[/TD]
[TD]Presentation Skills - July 26[/TD]
[TD]Completed[/TD]
[TD]26/07/2018[/TD]
[/TR]
</tbody>[/TABLE]












Unique value count --> Session
Condition 1 --> Course (Writing Skills)
Condition 2 --> Status (Completed)
Condition 3 --> Date range (May 1 - December 31, 2018)

Thus, I am trying to calculate how many total sessions (not counting duplicates) were held for Writing Skills that were completed from May 1 - December 31, 2018. In this case, it should be a total of 2 sessions during this date range.

I've been trying to figure this out for a while without success. Your assistance is greatly appreciated!

 

[LouisT]

assuming you want the first two entries to count as 2 since they're identical,
this will work: =COUNTIFS(B:B,"Writing Skills",D:D,"Completed",C:C,I1,E:E,">=05/01/2018",E:E,"<=12/31/2018")

 

[jim may]

[TABLE="width: 72"]
<tbody>[TR]
[TD="class: xl63, width: 72"]=COUNTIFS(B:B,"Writing Skills",D:D,"Completed",E:E,">="&DATE(2018,5,1),E:E,"<"&DATE(2018,12,31))[/TD]
[/TR]
</tbody>[/TABLE]

 

[RoxyS]

The answer I got was 3, however the answer should be 2. The trick is counting total unique values in column C (session) based on the 3 conditions outlined above. Since there are only two sessions (may 9 and 11) that were completed without counting duplicates, total sessions should be 2.

 

[RoxyS]

assuming you want the first two entries to count as 2 since they're identical,
this will work: =COUNTIFS(B:B,"Writing Skills",D:D,"Completed",C:C,I1,E:E,">=05/01/2018",E:E,"<=12/31/2018")

Hi, COUNTIFS does not work in this instance since I am working with a much larger data set than the example provided. The requirement is to be able to count unique values based on multiple conditions/criteria. In this case, the unique values I am looking for are in column C with conditions in columns B,D,and E.

 

[Marcelo Branco]

Assuming your data are in columns A:E and headers in row 1, try this array formula

=SUM(IF(FREQUENCY(IF(B2:B7="Writing Skills",IF(D2:D7="Completed",IF(E2:E7>=DATE(2018,5,1),IF(E2:E7<=DATE(2018,12,31),MATCH(C2:C7,C2:C7,0))))),ROW(C2:C7)-ROW(C2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[LouisT]

Hi, COUNTIFS does not work in this instance since I am working with a much larger data set than the example provided. The requirement is to be able to count unique values based on multiple conditions/criteria. In this case, the unique values I am looking for are in column C with conditions in columns B,D,and E.

This might just be outside my skillset, or it's not doable. This formula will count Unique values: =SUMPRODUCT(1/COUNTIF(C2:C7,C2:C7)) That you want in column C however i'm baffled how to use it in conjunction with the rest of the parameters.

Sorry
LouisT

 

[DanteAmor]

You can create an Auxiliary column in F and in cell M2 put the formula to count the only ones. See the example below

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M
1	Course	Session	Status	Date	Auxiliar		Course	Status	Date	Date		count --> Session
2	Writing Skills	Writing Skills - May 9	Completed	09/05/2018	1		Writing Skills	Completed	01/05/2018	31/12/2018		2
3	Writing Skills	Writing Skills - May 9	Completed	09/05/2018	2
4	Writing Skills	Writing Skills - Nov 11	Incomplete	01/11/2018	1
5	Writing Skills	Writing Skills - Nov 11	Completed	01/11/2018	1
6	Writing Skills	Writing Skills - Nov 11	Incomplete	01/11/2018	2
7	Presentation Skills	Presentation Skills - July 26	Completed	26/07/2018	1
Hoja4

 

[RoxyS]

Assuming your data are in columns A:E and headers in row 1, try this array formula

=SUM(IF(FREQUENCY(IF(B2:B7="Writing Skills",IF(D2:D7="Completed",IF(E2:E7>=DATE(2018,5,1),IF(E2:E7<=DATE(2018,12,31),MATCH(C2:C7,C2:C7,0))))),ROW(C2:C7)-ROW(C2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

That's it! This is SUPER helpful, thanks so much

 

[Marcelo Branco]

You are welcome. Glad to help

M.