Count a name once not multiple times

[Patriot2879]

Hi goodmorning, please can you help, I have a spreadsheet where it has peoplesnames and dates but for example there can be the same name on the same date inputtedmany times so Jo Bloggs on 1^st April could be listed 10 times, but Ionly want to count the name once and not 10 times please can you help with thisplease as the code below doesn’t work, it counts the name multiple times.

[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]=SUM(COUNTIFS('Activities-Complex'!C:C,"Jo Bloggs",'Activities-Complex'!AB:AB,D3)+SUM(COUNTIFS('Activities-Complex'!C:C,"JaneBloggs",'Activities-Complex'!AB:AB,D3)[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/CO[/COLOR][/SIZE][/FONT]DE]

 

[bobsan42]

I don't know exactly what you're trying to achieve but PivotTable's Distinct count can give you fast results.

 

[Patriot2879]

Hi I am trying to count names with dates, but I don't want the name if added 10 times for 1st april as an example to be counted as 10 times but I want this to be counted as 1, is this possible?

 

[sandy666]

post an example in table form of your raw data and expected result

 

[Patriot2879]

hi I have tried the DCOUNTA fomula but cant get to work
=DCOUNTA('Activities-Complex'!C:C"Jo Bloggs",'Activities-Complex'!AB:AB,D3)

 

[bobsan42]

setting the criteria for DCOUNT is a bit more complex than that.
Take a look here: https://support.office.com/en-gb/article/dcounta-function-00232a6d-5a66-4a01-a25b-c1653fda1244

 

[Patriot2879]

Hi I don't think that will work for me then, I hope you can still help me with this query.

 

[Patriot2879]

Hi, I think the =SUMRODUCT will work but don't know how to adapt into my previous formula. please can you help?

 

[Patriot2879]

I have tried this but it doesn't work

=SUMPRODUCT('Activities-Complex'!C:C<>"Jo Bloggs",'Activities-Complex'!AB:AB,C3)/COUNTIF('Activities-Complex'!C:C,"Jo Bloggs",'Activities-Complex'!AB:Sheet1!J15,C3))

 

[bobsan42]

if SUMIFS does it halfway through then maybe you can use this formula:

=MIN(1,[COLOR=#000000][FONT=Calibri]COUNTIFS('Activities-Complex'!C:C,"Jo Bloggs",'Activities-Complex'!AB:AB,D3))[/FONT][/COLOR]

so you will get either 1 or 0.