+ Reply to Thread
Results 1 to 5 of 5

Using defined name as criteria in formula

  1. #1
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2016
    Posts
    267

    Using defined name as criteria in formula

    Hi,

    I am using the following formula to look for grades that have been achieved across multiple sheets.

    Please Login or Register  to view this content.
    I have a list of grades that i have defined as the name GRDS.

    This is the list of grades
    1
    2
    3
    4
    5
    6
    7
    8
    9
    A*
    A
    B
    C
    D
    E
    F
    G
    P
    M
    D
    D*
    L1
    L2

    But for some reason it only seems to return the number of values of 1's? is this due to the grades being a combination of numbers and letters?

    Thanks

    Jamidd

  2. #2
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    423

    Re: Using defined name as criteria in formula

    Works OK in my test workbook -- with an important twist:

    =SUMPRODUCT(COUNTIFS(INDIRECT("'"&Sheets&"'!$B$12:$B$51"),A2,INDIRECT("'"&Sheets&"'!$F$12:$F$51"),SUBSTITUTE(GRDS,"*","~*")))

    Also, there are two D's in your GRDS: after C and after M.

  3. #3
    Forum Contributor
    Join Date
    02-07-2016
    Location
    England
    MS-Off Ver
    2016
    Posts
    267

    Re: Using defined name as criteria in formula

    Thank you for your response Root!

    Unfortunately, i can only seem to get the formula to work with 1's not the other grade values. If i place any of the other grades the formula does not seem to count? but if the grade is a 1 the formula does count!

    have you got an suggestions?

  4. #4
    Valued Forum Contributor Root_'s Avatar
    Join Date
    07-29-2017
    Location
    _
    MS-Off Ver
    2010+
    Posts
    423

    Re: Using defined name as criteria in formula

    Here is my test workbook -- try playing with it.
    Attached Files Attached Files

  5. #5
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Using defined name as criteria in formula

    I think your list of grades needs to be transposed.
    If it's currently A2:A24, make that list go A2:X2 instead..

    Edit, or the list of sheets as Root did.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Create a Formula for a defined Criteria
    By fareedexcel in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 06-03-2017, 04:24 AM
  2. Replies: 4
    Last Post: 03-14-2017, 09:24 PM
  3. [SOLVED] Sort by largest, 2nd criteria by less than average %, 3rd criteria pre defined heading.
    By deanusa in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-01-2015, 12:27 PM
  4. Replies: 3
    Last Post: 04-21-2014, 11:31 PM
  5. Application-defined or object-defined error with vba .formula
    By RogerKMiller in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-26-2013, 03:37 PM
  6. Macro to Insert defined integer into range defined by variable criteria
    By stereofeedback in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-06-2013, 12:33 PM
  7. Application-defined or Object-defined error on Formula entry
    By Kaigi in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-12-2009, 01:33 PM

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