count total among several tables

Vcoppens

Board Regular
Joined
Apr 16, 2012
Messages
90
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


RentalShort LeaseLong Lease
Column1ActualGoal%Column1ActualGoal%Column1ActualGoal%
John54125%Rose53167%Linda86133%
Mark44100%Linda43133%Rose76117%
Rose3475%Mark33100%Mark5683%
Linda2450%John2367%John3650%
1416350%1412467%2324383%
Off TargetOn TargetTotal Items
John213
Linda123
Mark123
Rose123

<tbody>
</tbody>
 
Last edited:
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!
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Forum statistics

Threads
1,213,536
Messages
6,114,207
Members
448,554
Latest member
Gleisner2

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
Back
Top