Countif with multiple criteria and duplicates counted once

dyexcel

New Member
Joined
Nov 13, 2018
Messages
3
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 byE NameC NameActivity date
GRANT AB HarrisAnne Hyslop21/08/2017Consider as a one client per day
GRANT AB HarrisAnne Hyslop21/08/2017Consider as a one client per day
GRANT AB HarrisAnne Hyslop30/08/2017
GRANT AB HarrisAnne Hyslop31/08/2017
GRANT AB HarrisBernadette Raabe10/05/2018
GRANT AB HarrisBernadette Raabe10/05/2018
GRANT AB HarrisBernadette Raabe10/05/2018
GRANT AB HarrisBernard Ivers7/09/2017
GRANT AB HarrisBernard Ivers8/09/2017
GRANT AB HarrisBernard Ivers8/01/2018
GRANT AB HarrisBeverley Boland10/05/2018
GRANT AB HarrisBeverley Boland17/05/2018
GRANT AB HarrisBeverley Boland18/06/2018
GRANT AB HarrisBeverley Boland20/06/2018
GRANT AB HarrisBeverley Boland21/06/2018
GRANT AB HarrisCarol Ethell30/10/2017
GRANT AB HarrisCarol Ethell31/10/2017
GRANT AB HarrisCarol Ethell1/11/2017
GRANT AB HarrisCarol Ethell1/11/2017
GRANT AB HarrisCarol Ethell2/11/2017
GRANT AB HarrisCarol Ethell3/11/2017
GRANT AB HarrisCaryn Arnel4/06/2018
GRANT AB HarrisCaryn Arnel5/06/2018
GRANT AB HarrisCaryn Arnel6/06/2018
GRANT AB HarrisCheryl Page22/02/2018
GRANT AB HarrisCheryl Page1/05/2018
GRANT BB HarrisFrank Knowles19/07/2017
GRANT BB HarrisFrank Knowles19/07/2017
GRANT BB HarrisFrank Knowles20/07/2017
GRANT BB HarrisFrank Knowles20/07/2017
GRANT BB HarrisFrank Knowles21/07/2017
GRANT BB HarrisGeorgina M Champney12/06/2018
GRANT BB HarrisGlenn Mansfield14/11/2017
GRANT BB HarrisHelen Rogovik11/07/2017
GRANT BB HarrisHugo Gracie6/07/2017
GRANT BB HarrisHugo Gracie7/07/2017
GRANT BB HarrisJanice Holland25/01/2018
GRANT BB HarrisJanice Holland7/02/2018
GRANT BB HarrisJanice Holland20/02/2018
GRANT BB HarrisJanice Holland2/03/2018
GRANT C (possible)B HarrisJit Bahadur Rai15/03/2018
GRANT C (possible)B HarrisJit Bahadur Rai15/03/2018
GRANT C (possible)B HarrisLaurna Love28/03/2018
GRANT C (possible)B HarrisLaurna Love29/03/2018
GRANT C (possible)B HarrisLaurna Love9/05/2018
GRANT C (possible)B HarrisLaurna Love21/05/2018
GRANT C (possible)B HarrisLaurna Love30/05/2018
GRANT C (possible)B HarrisLaurna Love25/06/2018
GRANT C (possible)B HarrisMackenna Murray2/03/2018
GRANT C (possible)B HarrisMichael Grant21/08/2017
GRANT C (possible)B HarrisPaul De Lio6/10/2017
GRANT DB HarrisArliyah Brown6/07/2017
GRANT DB HarrisArliyah Brown7/07/2017
GRANT DB HarrisArliyah Brown8/09/2017
GRANT DB HarrisArliyah Brown8/09/2017
GRANT DB HarrisArliyah Brown6/10/2017
GRANT DB HarrisArliyah Brown9/10/2017
GRANT DB HarrisArliyah Brown28/11/2017
GRANT DB HarrisArliyah Brown29/11/2017
GRANT DB HarrisArliyah Brown11/12/2017
GRANT DB HarrisArliyah Brown14/12/2017
GRANT DB HarrisArliyah Brown27/12/2017
GRANT DB HarrisAshley Scothern7/02/2018
GRANT DB HarrisAston Agostinho18/09/2017
GRANT DB HarrisAston Agostinho19/09/2017
GRANT DB HarrisAston Agostinho20/09/2017
GRANT DB HarrisAston Agostinho21/09/2017
GRANT DB HarrisAston Agostinho22/09/2017
GRANT DB HarrisAtylda Holman9/08/2017
GRANT DB HarrisAtylda Holman18/09/2017
GRANT DB HarrisAtylda Holman19/09/2017
GRANT DB HarrisAtylda Holman20/09/2017
GRANT AA JarrettJenny Kramaric (nee Clarke)25/07/2017
GRANT BA JarrettChris Smith13/10/2017
GRANT BA JarrettHelen Boardman13/10/2017
GRANT BA JarrettJack Bretherton13/10/2017
GRANT BA JarrettJenny Newell13/10/2017
GRANT BA JarrettLogan Stagg13/10/2017
GRANT BA JarrettMac Black13/10/2017
GRANT BA JarrettSamuel (Sam) MacDonald13/10/2017
GRANT CA JarrettDonna McKay21/11/2017
GRANT CA JarrettDonna McKay22/11/2017
GRANT C (possible)A JarrettCharmaine Kelly12/09/2017
GRANT DA JarrettAndrew ParkiGRANT Dn25/09/2017
GRANT DA JarrettAndrew ParkiGRANT Dn26/09/2017
GRANT DA JarrettAndrew ParkiGRANT Dn27/09/2017
GRANT DA JarrettAndrew ParkiGRANT Dn28/09/2017
GRANT DA JarrettAndrew ParkiGRANT Dn29/09/2017
GRANT DA JarrettAnthony Deeb27/11/2017
GRANT DA JarrettAnthony Deeb28/11/2017
GRANT DA JarrettAnthony Deeb29/11/2017
GRANT DA JarrettAnthony Deeb30/11/2017
GRANT DA JarrettAnthony Deeb1/12/2017
GRANT DA JarrettArliyah Brown4/07/2017
GRANT DA JarrettArliyah Brown5/07/2017
GRANT DA JarrettArliyah Brown6/07/2017
GRANT DA JarrettArliyah Brown7/07/2017
GRANT DA JarrettAston Agostinho18/09/2017
GRANT DA JarrettAston Agostinho19/09/2017
GRANT DA JarrettAston Agostinho20/09/2017
GRANT DA JarrettAston Agostinho21/09/2017
GRANT AJ HowlettStephen Watson12/09/2017
GRANT AJ HowlettStephen Watson13/09/2017
GRANT AJ HowlettStephen Watson9/10/2017
GRANT AJ HowlettStephen Watson31/01/2018
GRANT AJ HowlettStephen Watson1/02/2018
GRANT AJ HowlettStephen Watson8/02/2018
GRANT AJ HowlettStephen Watson8/02/2018
GRANT AJ HowlettStephen Watson12/02/2018
GRANT AJ HowlettStephen Watson13/02/2018
GRANT AJ HowlettTracy Spence9/08/2017
GRANT AJ HowlettTracy Spence15/08/2017
GRANT AJ HowlettTracy Spence11/09/2017
GRANT AJ HowlettTracy Spence12/09/2017
GRANT AJ HowlettTracy Spence13/09/2017
GRANT AJ HowlettTracy Spence3/10/2017
GRANT BJ HowlettAdrienne Watson31/08/2017
GRANT BJ HowlettAudrey (known as Joy) Huntley3/08/2017
GRANT BJ HowlettAudrey (known as Joy) Huntley30/08/2017
GRANT BJ HowlettBarbara Pollard9/10/2017
GRANT BJ HowlettBarbara Pollard10/10/2017
GRANT BJ HowlettBernard Gains26/06/2018
GRANT BJ HowlettBetty McColl10/08/2017
GRANT BJ HowlettBetty McColl31/08/2017
GRANT BJ HowlettBetty McColl11/10/2017
GRANT BJ HowlettBetty McColl17/10/2017
GRANT CJ HowlettShelley Alexander2/05/2018
GRANT CJ HowlettShelley Alexander3/05/2018
GRANT CJ HowlettShelley Alexander3/05/2018
GRANT CJ HowlettShelley Alexander3/05/2018
GRANT CJ HowlettShelley Alexander16/05/2018
GRANT CJ HowlettTroy Wengel1/05/2018
GRANT CJ HowlettTroy Wengel2/05/2018
GRANT CJ HowlettTroy Wengel3/05/2018
GRANT CJ HowlettTroy Wengel18/06/2018
GRANT CJ HowlettTroy Wengel21/06/2018
GRANT C (possible)J HowlettCharmaine Kelly11/09/2017
GRANT C (possible)J HowlettCharmaine Kelly12/09/2017
GRANT C (possible)J HowlettCharmaine Kelly13/09/2017
GRANT C (possible)J HowlettDarren Solly30/01/2018
GRANT C (possible)J HowlettDarren Solly1/02/2018
GRANT C (possible)J HowlettDylan Christensen - Sarnadsky5/04/2018
GRANT C (possible)J HowlettGraeme Raines1/05/2018
GRANT C (possible)J HowlettGraeme Raines4/05/2018
GRANT C (possible)J HowlettGraeme Raines4/05/2018
GRANT C (possible)J HowlettGraeme Raines21/06/2018
GRANT C (possible)J HowlettLeonard Jacobsen12/04/2018
GRANT C (possible)J HowlettLeonard Jacobsen3/05/2018
GRANT C (possible)J HowlettNathaniel Edwards8/11/2017
GRANT C (possible)J HowlettNathaniel Edwards8/11/2017
GRANT C (possible)J HowlettNathaniel Edwards15/11/2017
GRANT C (possible)J HowlettShelley Alexander18/06/2018
GRANT DJ HowlettAndrew ParkiGRANT Dn25/09/2017
GRANT DJ HowlettAndrew ParkiGRANT Dn26/09/2017
GRANT DJ HowlettAndrew ParkiGRANT Dn27/09/2017
GRANT DJ HowlettAndrew ParkiGRANT Dn28/09/2017
GRANT DJ HowlettAndrew ParkiGRANT Dn29/09/2017
GRANT DJ HowlettAnthony Deeb27/11/2017
GRANT DJ HowlettAnthony Deeb28/11/2017
GRANT DJ HowlettAnthony Deeb29/11/2017
GRANT DJ HowlettAnthony Deeb30/11/2017
GRANT DJ HowlettAnthony Deeb1/12/2017
GRANT DJ HowlettEmily Merchant25/09/2017
GRANT DJ HowlettEmily Merchant26/09/2017
GRANT DJ HowlettEmily Merchant27/09/2017
GRANT DJ HowlettEmily Merchant28/09/2017
GRANT DJ HowlettEmily Merchant29/09/2017

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

The answer is

GRANT AGRANT BGRANT CGRANT D
B Harris2212020
A Jarrett17218
J Howlett1410815

<colgroup><col span="6"></colgroup><tbody>
</tbody>


<tbody>
</tbody>

Thanks

Dyexcel
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,501
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Nov 13, 2018
Messages
3
Thank you very much Marcelo.

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

I am really appreciated your help.

Regards
DyExcel
 

Watch MrExcel Video

Forum statistics

Threads
1,127,524
Messages
5,625,309
Members
416,092
Latest member
dodovisk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top