COUNTA with condition and without duplicates

[pyniu]

Dear All,

I would like to count number of unique values without duplicates and with condition.
I spent many hours to find some answers about my issue but there wasn't clear and helpful info.

Let me introduce the problem and below data:
A2&A3 - list of persons
D:E - dates where some action was made by person
B2&B3 - counting in how many days person did action without duplicated values (values entered manually and i want to do it using formula)

*	A	B	C	D	E
person	no of days	*	aaa
aaa	*	aaa
bbb	*	aaa
TOTAL	*	aaa
*	*	*	aaa
*	*	*	aaa
*	*	*	bbb
*	*	*	bbb
*	*	*	bbb
*	*	*	bbb
*	*	*	bbb
*	*	*	bbb

<tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #ff0000, align: center"]3[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #ff0000, align: center"]2[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]4[/TD]

[TD="align: right"]2015-05-19[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]2015-05-19[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]2015-05-18[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]2015-05-21[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]2015-05-20[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]2015-05-20[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula B4 =SUMPRODUCT((D1:D12<>"")/COUNTIF(D1:D12;D1:D12&"")) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Comfy]

Here's one way using a helper column:

Excel 2010
	A	B	C	D	E	F
1	person	no of days	*	20/05/2015	aaa	1
2	aaa	3	*	20/05/2015	aaa	2
3	bbb	2	*	20/05/2015	aaa	3
4	TOTAL	5	*	19/05/2015	aaa	1
5	*	*	*	19/05/2015	aaa	2
6	*	*	*	18/05/2015	aaa	1
7	*	*	*	20/05/2015	bbb	1
8	*	*	*	21/05/2015	bbb	1
9	*	*	*	20/05/2015	bbb	2
10	*	*	*	20/05/2015	bbb	3
11	*	*	*	20/05/2015	bbb	4
12	*	*	*	20/05/2015	bbb	5
Sheet1

Cell Formulas
Range	Formula
B2	=COUNTIFS($E$1:$E$12,A2,$F$1:$F$12,1)
F1	=COUNTIFS($D$1:D1,D1,$E$1:E1,E1)

 

[pyniu]

Thank you Comfy,, it works correctly now

 

[Comfy]

Thank you Comfy it's good way, but i'll need to manually insert values to F column to determine number of days.

No you won't. The count in column F is determined by a formula.

Re-visist my post and you'll see the formula used in F.

 

[pyniu]

yes i see now, i didn't saw this or you editted it.
Anyway thank you very much for help, Cheers!

 

[Comfy]

yes i see now, i didn't saw this or you editted it.
Anyway thank you very much for help, Cheers!

You're Welcome.

I'm sure you can do it without a helper column.

 

[pyniu]

You're Welcome.

I'm sure you can do it without a helper column.

Possibly yes, but if this works with helper column it's no matter. The most important is that it works :P