Countif with multiple criteria and duplicates counted once

[dyexcel]

Hi,

how to write the formula for Countif with multiple criteria and duplicates counted once.

I want the number of client seen by E name as a criteria funded by. if E name seen the client multiple times a day then it should consider as a one client count.

I attached the sample table and answer table.

Funded by	E Name	C Name	Activity date
GRANT A	B Harris	Anne Hyslop	21/08/2017	Consider as a one client per day
GRANT A	B Harris	Anne Hyslop	21/08/2017	Consider as a one client per day
GRANT A	B Harris	Anne Hyslop	30/08/2017
GRANT A	B Harris	Anne Hyslop	31/08/2017
GRANT A	B Harris	Bernadette Raabe	10/05/2018
GRANT A	B Harris	Bernadette Raabe	10/05/2018
GRANT A	B Harris	Bernadette Raabe	10/05/2018
GRANT A	B Harris	Bernard Ivers	7/09/2017
GRANT A	B Harris	Bernard Ivers	8/09/2017
GRANT A	B Harris	Bernard Ivers	8/01/2018
GRANT A	B Harris	Beverley Boland	10/05/2018
GRANT A	B Harris	Beverley Boland	17/05/2018
GRANT A	B Harris	Beverley Boland	18/06/2018
GRANT A	B Harris	Beverley Boland	20/06/2018
GRANT A	B Harris	Beverley Boland	21/06/2018
GRANT A	B Harris	Carol Ethell	30/10/2017
GRANT A	B Harris	Carol Ethell	31/10/2017
GRANT A	B Harris	Carol Ethell	1/11/2017
GRANT A	B Harris	Carol Ethell	1/11/2017
GRANT A	B Harris	Carol Ethell	2/11/2017
GRANT A	B Harris	Carol Ethell	3/11/2017
GRANT A	B Harris	Caryn Arnel	4/06/2018
GRANT A	B Harris	Caryn Arnel	5/06/2018
GRANT A	B Harris	Caryn Arnel	6/06/2018
GRANT A	B Harris	Cheryl Page	22/02/2018
GRANT A	B Harris	Cheryl Page	1/05/2018
GRANT B	B Harris	Frank Knowles	19/07/2017
GRANT B	B Harris	Frank Knowles	19/07/2017
GRANT B	B Harris	Frank Knowles	20/07/2017
GRANT B	B Harris	Frank Knowles	20/07/2017
GRANT B	B Harris	Frank Knowles	21/07/2017
GRANT B	B Harris	Georgina M Champney	12/06/2018
GRANT B	B Harris	Glenn Mansfield	14/11/2017
GRANT B	B Harris	Helen Rogovik	11/07/2017
GRANT B	B Harris	Hugo Gracie	6/07/2017
GRANT B	B Harris	Hugo Gracie	7/07/2017
GRANT B	B Harris	Janice Holland	25/01/2018
GRANT B	B Harris	Janice Holland	7/02/2018
GRANT B	B Harris	Janice Holland	20/02/2018
GRANT B	B Harris	Janice Holland	2/03/2018
GRANT C (possible)	B Harris	Jit Bahadur Rai	15/03/2018
GRANT C (possible)	B Harris	Jit Bahadur Rai	15/03/2018
GRANT C (possible)	B Harris	Laurna Love	28/03/2018
GRANT C (possible)	B Harris	Laurna Love	29/03/2018
GRANT C (possible)	B Harris	Laurna Love	9/05/2018
GRANT C (possible)	B Harris	Laurna Love	21/05/2018
GRANT C (possible)	B Harris	Laurna Love	30/05/2018
GRANT C (possible)	B Harris	Laurna Love	25/06/2018
GRANT C (possible)	B Harris	Mackenna Murray	2/03/2018
GRANT C (possible)	B Harris	Michael Grant	21/08/2017
GRANT C (possible)	B Harris	Paul De Lio	6/10/2017
GRANT D	B Harris	Arliyah Brown	6/07/2017
GRANT D	B Harris	Arliyah Brown	7/07/2017
GRANT D	B Harris	Arliyah Brown	8/09/2017
GRANT D	B Harris	Arliyah Brown	8/09/2017
GRANT D	B Harris	Arliyah Brown	6/10/2017
GRANT D	B Harris	Arliyah Brown	9/10/2017
GRANT D	B Harris	Arliyah Brown	28/11/2017
GRANT D	B Harris	Arliyah Brown	29/11/2017
GRANT D	B Harris	Arliyah Brown	11/12/2017
GRANT D	B Harris	Arliyah Brown	14/12/2017
GRANT D	B Harris	Arliyah Brown	27/12/2017
GRANT D	B Harris	Ashley Scothern	7/02/2018
GRANT D	B Harris	Aston Agostinho	18/09/2017
GRANT D	B Harris	Aston Agostinho	19/09/2017
GRANT D	B Harris	Aston Agostinho	20/09/2017
GRANT D	B Harris	Aston Agostinho	21/09/2017
GRANT D	B Harris	Aston Agostinho	22/09/2017
GRANT D	B Harris	Atylda Holman	9/08/2017
GRANT D	B Harris	Atylda Holman	18/09/2017
GRANT D	B Harris	Atylda Holman	19/09/2017
GRANT D	B Harris	Atylda Holman	20/09/2017
GRANT A	A Jarrett	Jenny Kramaric (nee Clarke)	25/07/2017
GRANT B	A Jarrett	Chris Smith	13/10/2017
GRANT B	A Jarrett	Helen Boardman	13/10/2017
GRANT B	A Jarrett	Jack Bretherton	13/10/2017
GRANT B	A Jarrett	Jenny Newell	13/10/2017
GRANT B	A Jarrett	Logan Stagg	13/10/2017
GRANT B	A Jarrett	Mac Black	13/10/2017
GRANT B	A Jarrett	Samuel (Sam) MacDonald	13/10/2017
GRANT C	A Jarrett	Donna McKay	21/11/2017
GRANT C	A Jarrett	Donna McKay	22/11/2017
GRANT C (possible)	A Jarrett	Charmaine Kelly	12/09/2017
GRANT D	A Jarrett	Andrew ParkiGRANT Dn	25/09/2017
GRANT D	A Jarrett	Andrew ParkiGRANT Dn	26/09/2017
GRANT D	A Jarrett	Andrew ParkiGRANT Dn	27/09/2017
GRANT D	A Jarrett	Andrew ParkiGRANT Dn	28/09/2017
GRANT D	A Jarrett	Andrew ParkiGRANT Dn	29/09/2017
GRANT D	A Jarrett	Anthony Deeb	27/11/2017
GRANT D	A Jarrett	Anthony Deeb	28/11/2017
GRANT D	A Jarrett	Anthony Deeb	29/11/2017
GRANT D	A Jarrett	Anthony Deeb	30/11/2017
GRANT D	A Jarrett	Anthony Deeb	1/12/2017
GRANT D	A Jarrett	Arliyah Brown	4/07/2017
GRANT D	A Jarrett	Arliyah Brown	5/07/2017
GRANT D	A Jarrett	Arliyah Brown	6/07/2017
GRANT D	A Jarrett	Arliyah Brown	7/07/2017
GRANT D	A Jarrett	Aston Agostinho	18/09/2017
GRANT D	A Jarrett	Aston Agostinho	19/09/2017
GRANT D	A Jarrett	Aston Agostinho	20/09/2017
GRANT D	A Jarrett	Aston Agostinho	21/09/2017
GRANT A	J Howlett	Stephen Watson	12/09/2017
GRANT A	J Howlett	Stephen Watson	13/09/2017
GRANT A	J Howlett	Stephen Watson	9/10/2017
GRANT A	J Howlett	Stephen Watson	31/01/2018
GRANT A	J Howlett	Stephen Watson	1/02/2018
GRANT A	J Howlett	Stephen Watson	8/02/2018
GRANT A	J Howlett	Stephen Watson	8/02/2018
GRANT A	J Howlett	Stephen Watson	12/02/2018
GRANT A	J Howlett	Stephen Watson	13/02/2018
GRANT A	J Howlett	Tracy Spence	9/08/2017
GRANT A	J Howlett	Tracy Spence	15/08/2017
GRANT A	J Howlett	Tracy Spence	11/09/2017
GRANT A	J Howlett	Tracy Spence	12/09/2017
GRANT A	J Howlett	Tracy Spence	13/09/2017
GRANT A	J Howlett	Tracy Spence	3/10/2017
GRANT B	J Howlett	Adrienne Watson	31/08/2017
GRANT B	J Howlett	Audrey (known as Joy) Huntley	3/08/2017
GRANT B	J Howlett	Audrey (known as Joy) Huntley	30/08/2017
GRANT B	J Howlett	Barbara Pollard	9/10/2017
GRANT B	J Howlett	Barbara Pollard	10/10/2017
GRANT B	J Howlett	Bernard Gains	26/06/2018
GRANT B	J Howlett	Betty McColl	10/08/2017
GRANT B	J Howlett	Betty McColl	31/08/2017
GRANT B	J Howlett	Betty McColl	11/10/2017
GRANT B	J Howlett	Betty McColl	17/10/2017
GRANT C	J Howlett	Shelley Alexander	2/05/2018
GRANT C	J Howlett	Shelley Alexander	3/05/2018
GRANT C	J Howlett	Shelley Alexander	3/05/2018
GRANT C	J Howlett	Shelley Alexander	3/05/2018
GRANT C	J Howlett	Shelley Alexander	16/05/2018
GRANT C	J Howlett	Troy Wengel	1/05/2018
GRANT C	J Howlett	Troy Wengel	2/05/2018
GRANT C	J Howlett	Troy Wengel	3/05/2018
GRANT C	J Howlett	Troy Wengel	18/06/2018
GRANT C	J Howlett	Troy Wengel	21/06/2018
GRANT C (possible)	J Howlett	Charmaine Kelly	11/09/2017
GRANT C (possible)	J Howlett	Charmaine Kelly	12/09/2017
GRANT C (possible)	J Howlett	Charmaine Kelly	13/09/2017
GRANT C (possible)	J Howlett	Darren Solly	30/01/2018
GRANT C (possible)	J Howlett	Darren Solly	1/02/2018
GRANT C (possible)	J Howlett	Dylan Christensen - Sarnadsky	5/04/2018
GRANT C (possible)	J Howlett	Graeme Raines	1/05/2018
GRANT C (possible)	J Howlett	Graeme Raines	4/05/2018
GRANT C (possible)	J Howlett	Graeme Raines	4/05/2018
GRANT C (possible)	J Howlett	Graeme Raines	21/06/2018
GRANT C (possible)	J Howlett	Leonard Jacobsen	12/04/2018
GRANT C (possible)	J Howlett	Leonard Jacobsen	3/05/2018
GRANT C (possible)	J Howlett	Nathaniel Edwards	8/11/2017
GRANT C (possible)	J Howlett	Nathaniel Edwards	8/11/2017
GRANT C (possible)	J Howlett	Nathaniel Edwards	15/11/2017
GRANT C (possible)	J Howlett	Shelley Alexander	18/06/2018
GRANT D	J Howlett	Andrew ParkiGRANT Dn	25/09/2017
GRANT D	J Howlett	Andrew ParkiGRANT Dn	26/09/2017
GRANT D	J Howlett	Andrew ParkiGRANT Dn	27/09/2017
GRANT D	J Howlett	Andrew ParkiGRANT Dn	28/09/2017
GRANT D	J Howlett	Andrew ParkiGRANT Dn	29/09/2017
GRANT D	J Howlett	Anthony Deeb	27/11/2017
GRANT D	J Howlett	Anthony Deeb	28/11/2017
GRANT D	J Howlett	Anthony Deeb	29/11/2017
GRANT D	J Howlett	Anthony Deeb	30/11/2017
GRANT D	J Howlett	Anthony Deeb	1/12/2017
GRANT D	J Howlett	Emily Merchant	25/09/2017
GRANT D	J Howlett	Emily Merchant	26/09/2017
GRANT D	J Howlett	Emily Merchant	27/09/2017
GRANT D	J Howlett	Emily Merchant	28/09/2017
GRANT D	J Howlett	Emily Merchant	29/09/2017

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

The answer is GRANT A GRANT B GRANT C GRANT D B Harris 22 12 0 20 A Jarrett 1 7 2 18 J Howlett 14 10 8 15 <colgroup><col span="6"></colgroup><tbody> </tbody>

<tbody>
</tbody>

Thanks

Dyexcel

 

[Marcelo Branco]

Welcome to Mr Excel forum

Assuming your data in A1:D168, headers in row 1, try

	F​	G​	H​	I​	J​
1​		GRANT A​	GRANT B​	GRANT C​	GRANT D​
2​	B Harris​	22​	12​	0​	20​
3​	A Jarrett​	1​	7​	2​	18​
4​	J Howlett​	14​	10​	8​	15​

<tbody>
</tbody>

Array formula in G2 copied across and down
=SUM(IF(FREQUENCY(IF($A$2:$A$168=G$1,IF($B$2:$B$168=$F2,MATCH($C$2:$C$168&"|"&$D$2:$D$168,$C$2:$C$168&"|"&$D$2:$D$168,0))),ROW($C$2:$C$168)-ROW($C$2)+1),1))
confirmed with Ctrl+Shift+Enter, not just Enter

Hope this helps

M.

 

[dyexcel]

Thank you very much Marcelo.

It is working fine, but I need some time to understand.

I am really appreciated your help.

Regards
DyExcel

 

[Marcelo Branco]

You are welcome. Glad to help.

M.