count total among several tables

[Vcoppens]

Hi,

I have a workbook with several sheets. The example used below is very simplified

Each sheet of the workbook has several tables. Each table is composed the same way but there might be more or less names in the tables per sheet. For example sheet #1 might have 4 people. Sheet #2 might have 6 etc. (in reality there are many more and each sheet has 10 tables: all the same people but different topics: I used "rental" - "short lease" - "long lease" for name sake. The next sheet has also those 10 tables but might have a shorter or longer name list.

Per sheets I need to count per person for how many items they are on target (100% or above), for how many they are off target (100% or below).

I have tried with countif but selecting each list of name and match it with the % below or above results in an tremendous formula.

In addition the name in the reference table is slightly off from the result table (a feature that cannot be changed right now). For example the reference table might have J. Smith - S. Smith. The table where you count the result per name states John Smith - Susan Smith. I know how to get around this error by using "*"& &"*" and then I can change those few people manually. That however is the least of my worries.

Counting how many or on target and off target through and excel formula would be a tremendous help.

Any input would be greatly appreciated.


The last table is the result I am looking for - basically for which I would like to create a formula - right now I just entered it manually

Rental

<tbody>
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 77"][/TD]
[TD="width: 128, colspan: 2"]Short Lease[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 77"][/TD]
[TD="width: 128, colspan: 2"]Long Lease[/TD]
[TD="width: 64"][/TD]

[TD="class: xl67"]Column1[/TD]
[TD="class: xl68"]Actual[/TD]
[TD="class: xl68"]Goal[/TD]
[TD="class: xl69"]%[/TD]

[TD="class: xl67"]Column1[/TD]
[TD="class: xl68"]Actual[/TD]
[TD="class: xl68"]Goal[/TD]
[TD="class: xl69"]%[/TD]

[TD="class: xl67"]Column1[/TD]
[TD="class: xl68"]Actual[/TD]
[TD="class: xl68"]Goal[/TD]
[TD="class: xl69"]%[/TD]

[TD="class: xl65"]John[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl66, align: right"]125%[/TD]

[TD="class: xl65"]Rose[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl66, align: right"]167%[/TD]

[TD="class: xl65"]Linda[/TD]
[TD="class: xl63, align: right"]8[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl66, align: right"]133%[/TD]

[TD="class: xl65"]Mark[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl66, align: right"]100%[/TD]

[TD="class: xl65"]Linda[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl66, align: right"]133%[/TD]

[TD="class: xl65"]Rose[/TD]
[TD="class: xl63, align: right"]7[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl66, align: right"]117%[/TD]

[TD="class: xl65"]Rose[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]4[/TD]
[TD="class: xl66, align: right"]75%[/TD]

[TD="class: xl65"]Mark[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl63, align: right"]3[/TD]
[TD="class: xl66, align: right"]100%[/TD]

[TD="class: xl65"]Mark[/TD]
[TD="class: xl63, align: right"]5[/TD]
[TD="class: xl63, align: right"]6[/TD]
[TD="class: xl66, align: right"]83%[/TD]

[TD="class: xl70"]Linda[/TD]
[TD="class: xl71, align: right"]2[/TD]
[TD="class: xl71, align: right"]4[/TD]
[TD="class: xl72, align: right"]50%[/TD]

[TD="class: xl70"]John[/TD]
[TD="class: xl71, align: right"]2[/TD]
[TD="class: xl71, align: right"]3[/TD]
[TD="class: xl72, align: right"]67%[/TD]

[TD="class: xl70"]John[/TD]
[TD="class: xl71, align: right"]3[/TD]
[TD="class: xl71, align: right"]6[/TD]
[TD="class: xl72, align: right"]50%[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]16[/TD]
[TD="class: xl64, align: right"]350%[/TD]

[TD="align: right"]14[/TD]
[TD="align: right"]12[/TD]
[TD="class: xl64, align: right"]467%[/TD]

[TD="align: right"]23[/TD]
[TD="align: right"]24[/TD]
[TD="class: xl64, align: right"]383%[/TD]

[TD="class: xl63"][/TD]
[TD="class: xl73"]Off Target[/TD]
[TD="class: xl74"]On Target[/TD]
[TD="colspan: 2"]Total Items[/TD]

[TD="class: xl63"]John[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl63"]Linda[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl63"]Mark[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="align: right"]3[/TD]

[TD="class: xl63"]Rose[/TD]
[TD="class: xl63, align: right"]1[/TD]
[TD="class: xl63, align: right"]2[/TD]
[TD="align: right"]3[/TD]

</tbody>

 

[Vcoppens]

Hi @bosco, I implemented the formula at work and it doesn’t work.
As I need to divide the 10 tables into 2 separate sub counting, I cannot drag thru to the last table. I therefore select the first 3 tables, then hold the Ctrl key and select the % rate columns of the next 2 tables.
When I click on the formula icon it seems to have and issue with the section that is selected thru Ctrl. I tried using ( ) and [ ] but it won’t work.
Would you have any suggestions?
Thank you!