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>

 

[tico_ocit]

Hi there,

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

[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Off target[/TD]
[TD]Off target[/TD]
[TD]Off target[/TD]
[TD]On target[/TD]
[TD]On target[/TD]
[TD]On Target[/TD]
[TD]Total Items[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Rental[/TD]
[TD]Short Lease[/TD]
[TD]Longe Lease[/TD]
[TD]Rental[/TD]
[TD]Short Lease[/TD]
[TD]Longe Lease[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Linda[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Rose[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Sam_D_Ben]

May be this.

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Rental					Short Lease					Long Lease
2	Name	Actual	Goal	%		Name	Actual	Goal	%		Name	Actual	Goal	%
3	John	5	4	125%		Rose	5	3	167%		Linda	8	6	133%
4	Mark	4	4	100%		Linda	4	3	133%		Rose	7	6	117%
5	Rose	3	4	75%		Mark	3	3	100%		Mark	5	6	83%
6	Linda	2	4	50%		John	2	3	67%		John	3	6	50%
7
8	Name	Off Target	On Target	Total Items
9	John	2	1	3
10	Linda	1	2	3
11	Mark	1	2	3
12	Rose	1	2	3
Sheet5

Cell Formulas
Range	Formula
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)

 

[bosco_yip]

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

 

[Vcoppens]

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!

 

[Vcoppens]

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.

 

[Vcoppens]

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)

 

[Vcoppens]

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

 

[Sam_D_Ben]

COUNTIFS($D$3:$N$6,$D$10:$N$13),"<100%",($A$3:$K$6,$A$10:$K$13,$A23)

May be something like this.

=COUNTIFS($D$3:$N$13,"<1",$A$3:$K$13,$A23)

 

[Vcoppens]

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,