+ Reply to Thread
Results 1 to 13 of 13

Named Ranges & Functions - Problem

  1. #1
    Registered User
    Join Date
    12-02-2011
    Location
    Greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    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...

  2. #2
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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>
    DILIPandey
    +91 9810929744
    dilipandey@gmail.com

  3. #3
    Registered User
    Join Date
    12-02-2011
    Location
    Greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    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. #4
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Named Ranges & Functions - Problem

    Hi teo and welcome to the forum

    COUNTIFS..in Excel 2003??

    Would you like to upload a sample workbook??
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  5. #5
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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. #6
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Named Ranges & Functions - Problem

    I upload for you an example, using SUMPRODUCT function.

    Hope to helps you.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-02-2011
    Location
    Greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    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. #8
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Named Ranges & Functions - Problem

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

    Glad to found your solution!

    Ths for reb*

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Named Ranges & Functions - Problem

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

    Glad to found your solution!

    Ths for reb*

  10. #10
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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. #11
    Forum Expert dilipandey's Avatar
    Join Date
    12-05-2011
    Location
    Dubai, UAE
    MS-Off Ver
    1997 - 2016
    Posts
    8,191

    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. #12
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,361

    Re: Named Ranges & Functions - Problem

    Quote Originally Posted by teo_7 View Post
    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.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    12-02-2011
    Location
    Greece
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Named Ranges & Functions - Problem

    bebo that was great help,
    thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1