Calculate percentage of client base

[Lesjoan01]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Client

[/TD]
[TD]When became a client
[/TD]
[TD]How long been a client
[/TD]
[TD]%
[/TD]
[/TR]
[TR]
[TD]ABC
[/TD]
[TD]09/03/2017
[/TD]
[TD]1 year 10 months
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]DEF
[/TD]
[TD]30/04/2013
[/TD]
[TD]5 years 9 months
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GHI
[/TD]
[TD]13/11/2007
[/TD]
[TD]11 years 2 months
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JKL
[/TD]
[TD]30/08/2018
[/TD]
[TD]0 years 5 months

[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Hi,
I am trying to work out the following:

I have a database of Clients and I have how long they have been a client of the business. I want to work out percentages based on:

% of clients who have been with the business less than one year, between 1 to 5 years, between 5 to 10 years and finally over 10 years.

There are 3345 active clients. So I would want to know the total % for each of the above criteria - if that makes sense?

Any ideas?

Many thanks

Lesley

 

[AlanY]

will you consider to split the 'how long' column into 2 with years & months?

 

[AlanY]

this solution based on the 'how long' column with a different format that can be hidden

Book1
	A	B	C	D	E	F
1	Client	When became a client	How long been a client		%
2	ABC	09/03/2017	1.9	<	1	2
3	DEF	30/04/2013	5.8	1-	5	3
4	GHI	13/11/2007	11.2	5-	10	8
5	JKL	30/08/2018	0.4	10-	99	11
6	C1	10/01/1998	21.1
7	C2	24/03/2017	1.9
8	C3	27/01/2001	18.0
9	C4	29/08/2009	9.4
10	C5	21/08/2018	0.4
11	C6	28/07/1999	19.5
12	C7	30/01/2010	9.0
13	C8	09/08/1999	19.5
14	C9	13/02/1998	21.0
15	C10	03/08/2012	6.5
16	C11	09/07/2010	8.6
17	C12	19/04/1994	24.8
18	C13	02/01/2018	1.1
19	C14	12/04/2005	13.8
20	C15	25/12/2013	5.1
21	C16	22/11/2003	15.2
22	C17	20/06/2000	18.6
23	C18	18/12/2010	8.1
24	C19	12/08/1995	23.5
25	C20	07/06/2012	6.6
Sheet3

Cell Formulas
Range	Formula
C2	=(TODAY()-B2)/365.25
F2	=COUNTIF($C$2:$C$25,"<"&E2)-SUM($F$1:F1)