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:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
Hi there,

at first sight it will be much easier if you convert the last table into something like this:

Off targetOff targetOff targetOn targetOn targetOn TargetTotal Items
RentalShort LeaseLonge LeaseRentalShort LeaseLonge Lease
John
Linda
Mark
Rose

<tbody>
</tbody>

Of course you can merge the "Off target" and "On Target" cells.
With this change you can set each cell to go for that specific sheet and return the value that you want.

Will be reasonable to you, to transform the table? :)

Hope I could help.
 
Upvote 0
May be this.


Excel 2013/2016
ABCDEFGHIJKLMN
1RentalShort LeaseLong Lease
2NameActualGoal%NameActualGoal%NameActualGoal%
3John54125%Rose53167%Linda86133%
4Mark44100%Linda43133%Rose76117%
5Rose3475%Mark33100%Mark5683%
6Linda2450%John2367%John3650%
7
8NameOff TargetOn TargetTotal Items
9John213
10Linda123
11Mark123
12Rose123
Sheet5
Cell Formulas
RangeFormula
B9=IF(VLOOKUP(A9,$A$2:$D$6,4,0)<100%,1,0)+IF(VLOOKUP(A9,$F$2:$I$6,4,0)<100%,1,0)+IF(VLOOKUP(A9,$K$2:$N$6,4,0)<100%,1,0)
C9=IF(VLOOKUP(A9,$A$2:$D$6,4,0)>=100%,1,0)+IF(VLOOKUP(A9,$F$2:$I$6,4,0)>=100%,1,0)+IF(VLOOKUP(A9,$K$2:$N$6,4,0)>=100%,1,0)
D9=SUM(B9:C9)
 
Last edited:
Upvote 0
Another option, by using Post #.03 table,

[B9] =COUNTIFS($D$3:$N$6,"<1",$A$3:$K$6,$A9)

[C9] =COUNTIFS($D$3:$N$6,">=1",$A$3:$K$6,$A9)

[D9] =B9+C9

All formulas copied down

Regards
Bosco
 
Upvote 0
Thank you very much for your replies!
I will be testing them out over the weekend and see which was best fitted to extract what I needed.

Thank you in advance!
 
Upvote 0
Hi every one!
@tico_ocit: thank you for the suggestion to change the table - unfortunately they want to keep the layout as it is.

@Sam_D_ Ben and @bosco_yip: these were 2 wonderful formula suggestions. I tested them on my simplified spreadsheet and both worked as a charm. I will implement at work on Monday and see which one needs the least manipulation: both source and final result are in 2 different workbooks.

Thank you to all for your great insights - now ready to enjoy my weekend. Hope yours will be great too.
 
Upvote 0
Hi Bosco, I have a small question: in my example I showed 3 tables. The remaining tables are sorted below (i.e. 3 tables from row 1 thru 6, the next 3 from row 7 thru row 12,...)

Holding ctrl to go to the next rows gives an error in my formula ...

Would you be able to help with this?

Thank you in advance

COUNTIFS($D$3:$N$6,$D$10:$N$13),"<100%",($A$3:$K$6,$A$10:$K$13,$A23)
 
Upvote 0
edited: there are 10 tables in total sorted in the sheet as follows
3 tables
3 talbes
3 tables
1 table and to the right, on the same row level, a table with a different layout so a drag down till table 10 doesn't really work because of the non-similar table to the right of it ...

--- Dragging down works! Even if the table to the right is not in the same layout as the others - sorry for the bother
 
Last edited:
Upvote 0
Hi @Sam D Ben, thank you for your suggestion. On my test workbook I was able to drag completely down to the 10th table (basically including the irrelevant table to the right of this last table. I will test it tomorrow at work and see if it works there too.
Kind regards,
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,210
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