Counting values from multiple tables (ranges) with same fields but differing records

[DaiB]

Hi folks, I’m after some help with a counting problem I can’t solve. Unsure if this should be a pivot table or not, happy to move the thread if needed.

Situation:
1 spreadsheet
Always 8 tabs (example only has 2) representing each Team
Each tab has a table (actually a range, not a real excel “table”) which has 8 fields/columns exactly the same (example only has 4 not including totals) but different records/rows (different quantity of rows, and different data in rows)

I’ve attached a spreadsheet that hopefully represents the above
Columns in each “Team” tab are the same
Rows are different, both the number of rows and and the info in each row.

I want to count the people, in each team, doing each job - but in separate role tables. The result tab in the example hopefully explains this.

My problem is dealing with the different number of rows - I can’t simply make a table that copies data from each tab because the rows change all the time. If a new one is added, the results table wouldn’t see it until I went and adjusted the table again.

Can anyone help?

[wk9128]

worksheet or Tab name : Result

Cell B3 formula , Drag down and across

Please Login or Register  to view this content.

[nick.williams]

That doesn't work for me; always uses role 1. How about:

=IFERROR(SUMIF(INDEX(INDIRECT("'"&B$2&"'!B3:D6"),MATCH($A3,INDIRECT("'"&B$2&"'!A3:A6"),),MATCH(INDEX($A:$A,MAX(IF($A$1:$A3="",ROW($A$1:$A3),0))-1),INDIRECT("'"&B$2&"'!B2:D2"),)),"<>"),"")

This looks up from the current row, finds the first blank row, and uses the row above that, to get the Role.

Also note that the ranges B3:D6 and A3:A6 could be changed to B:D and A:A. Although if performance is an issue then you could just set it to something like 100 rows or whatever is comfortably above the data you will have in any sheet.