# Named Ranges & Functions - Problem

1. ## 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?

2. ## Re: Named Ranges & Functions - Problem

Hi teo,

Welcome to the forum.

In Countifs function, you forgot to mention criteria after mentioning it once.
See the syntax below and adjust accordingly, in case of any issue post the sample file
COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2]…)

Regards,
DILIPandey

<click on below 'star' if this helps>

3. ## Re: Named Ranges & Functions - Problem

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

4. ## Re: Named Ranges & Functions - Problem

Hi teo and welcome to the forum

COUNTIFS..in Excel 2003??

Would you like to upload a sample workbook??

5. ## Re: Named Ranges & Functions - Problem

Hi teo,

Basis your example, it would be :-

Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000

Hope this helps.
Like I said before, upload a sample file, if you have any issues

Regards,
DILIPandey

<click on below 'star' if this helps>

6. ## Re: Named Ranges & Functions - Problem

I upload for you an example, using SUMPRODUCT function.

Hope to helps you.

7. ## Re: Named Ranges & Functions - Problem

oh thank you guys both!
you saved my day!

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

best
t.

8. ## Re: Named Ranges & Functions - Problem

You are welcome(Παρακαλώ!!)

Ths for reb*

9. ## Re: Named Ranges & Functions - Problem

You are welcome(Παρακαλώ!!)

Ths for reb*

10. ## Re: Named Ranges & Functions - Problem

Hi teo,

Basis your example, it would be :-

Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000

Hope this helps.
Like I said before, upload a sample file, if you have any issues

Regards,
DILIPandey

<click on below 'star' if this helps>

11. ## Re: Named Ranges & Functions - Problem

Hi teo,

Basis your example, it would be :-

Criteria Range--- Criteria
Table45-------- 45
Children-------- 2
Salary---------- 15000

Hope this helps.
Like I said before, upload a sample file, if you have any issues

Regards,
DILIPandey

<click on below 'star' if this helps>

12. ## Re: Named Ranges & Functions - Problem

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.

13. ## Re: Named Ranges & Functions - Problem

bebo that was great help,
thanks a lot

There are currently 1 users browsing this thread. (0 members and 1 guests)