Named Ranges & Functions - Problem

Hi there,

I would appreciate any help...

I have a worksheet with many named tables (the names are given automatically through a VBA script) updated in a regular basis with the same labels at each first row.

Let's say
A1:E12 is TABLE1,
A13:E48 is TABLE2...
A1200:E1261 is TABLE90 etc.

Because all columns' data are similar (ie Column A is Name, Column B is Age, Column C is ID, Column D is Salary, Column E is Children) I've named the entire columns with it's label. So the entire Column A is Name, the entire Column B is Age...

So, for example if I want to find at TABLE45 how many people are aged 45+ with 2 children and salary 15.000, what is the proper syntax of COUNTIFS or any other function?
I tried COUNTIFS(TABLE45;Age>45;Children=2) but it doesn't work.

Any help?

Thanx for your time spend in advance...

Thanks dilipandey for your quick response,

let me ask you: if criteria_range1 is TABLE45 then criteria 1 is what? for the rest I understand, criteria_range2 = AGE, criteria2 = >45

thanks again

oh thank you guys both!
you saved my day!

sorry for the delay and uploading file, this was my first post

best
t.

Originally Posted by teo_7

I have a worksheet with many named tables (the names are given automatically through a VBA script) updated in a regular basis with the same labels at each first row.Let's say
A1:E12 is TABLE1,
A13:E48 is TABLE2...
A1200:E1261 is TABLE90 etc....

I think there are reasons needed to name multi ranges, i.e TABLE1 for list of employees of AA company, TABLE2 for BB company ...
As per Fortis1991 in #6, column name (AGE45, SALARY45 ...) were fixed to TABLE45 only.
What happen if seaching to TABLE90 or others?
Therefore, in general, my solution is:
1- Name all the column ACROSS all table (AGE, SALARY, CHILDREN)
2- Crossing the TABLE and column with criteria like this: (TABLE45 AGE>=45)*(TABLE45 SALARY=15000)...then use SUMPRODUCT to count.
So, the formula is:

Please Login or Register  to view this content.

with H1: Table Name (TABLE45)
H2 = Age (45)
H3 = Salary (15000)
H4 = Children (2)
User can change H1,2,3,4 to get expected result.

bebo that was great help,
thanks a lot