Functional counting specific categories of data without adding to the data / Frequency

[Bedlam]

Hi

I'm hoping for some help to create some stats on the status of working through client jobs. First I must state that I can't use a pivot table or Visual Basic solution as there is a shared workbook which I don't control. I've simplified client on boarding in an example. I was wondering if there is some way I could generate the stats without having to resort to building column of functions alongside the data.

The basic scenario is that periodically client's data must be reviewed. A client may have one office (known as an entity) or may have many. It makes sense to perform all the checks simultaneously with a list for a team to work through. For simplicity I've assumed for each entity a review moves between stages 1-5 before turning complete. If a client has only one entity and that is complete then that client is fully complete. If a client has multiple entities and some but not all are complete then the client may be said to be partially complete. If a client has nothing that has reached complete then they have the status Nothing Completed.

I had previously used a CSE function elsewhere and wondered could something like this be used and adjusted
Example 1

Copy of PR tracker Feb 180220.xlsx
	A	B	C
10	Completed	46	26
Stats

Cell Formulas
Range	Formula
B10	B10	=COUNTIF('Feb 2020'!AN:AN,A10)
C10	C10	=SUM(--(FREQUENCY(IF('Feb 2020'!$AN$2:$AN$99999=A10,'Feb 2020'!$O$2:$O$99999),'Feb 2020'!$O$2:$O$99999)>0))

Example 2 - Sample data

Fully Complete.xlsx
	A	B	C	D	E	F	G
1	Data				Notes to Explain
2	Client	Sub Entity	Stage		Client	Status
3	1	New York	Complete		1	Fully Complete	i.e. all Sub Entities are complete
4	2	London	1		2	Partially Complete	i.e. some Sub Entities are complete
5	2	Paris	Complete		23	Fully Complete	i.e. all Sub Entities are complete
6	23	Sydney	Complete		463	Partially Complete	i.e. some Sub Entities are complete
7	23	Dubai	Complete		5555	Nothing Completed	i.e. no Sub Entities are complete
8	23	New York	Complete
9	463	London	1		Results
10	463	Paris	2		Clients	5
11	463	Sydney	3		Fully Complete	2	i.e. Clients 1 and 23
12	463	Tokyo	Complete		Partially Complete	2	i.e. Clients 2 and 463
13	5555	Paris	1		Nothing Completed	1	i.e. Clients 5555
14	5555	Singapore	2
15	5555	Tokyo	3		Complete	4	i.e. 4 Clients have a complete stage
16	5555	New York	4
17	5555	London	5
Sheet1

Hoping someone can suggest how best to do this. Thanks in advance.

 

[Eric W]

Start with the F10 formula and look up the FREQUENCY function. The other 2 formulas are just variations on F10.

Happy to help!