Formulas: referring to specific column in named range

  • Hi All,

    Is there a way of referring to a specific column in a named range in a formula?

    I have a range named "Database" that is 37 columns wide and a variable # of rows deep. I want to write a formula that finds all instances of a text string in column 5 of "Database and adds up the corresponding values in column 7 - essentially a sumif using co-ordinates to a named range.

    Any thoughts?



  • Try something like this (and just change the string you are matching):


    Best regards,

    Barrie Davidson
    My Excel Web Page
    "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

  • Jen:

    Along the lines of Barrie's suggestion--funny that I'd never run across this need myself before:

    Assuming you have a range named "Database":

    =SUM(OFFSET(Database, 0, 4)) - SUM(OFFSET( Database, 0, 5))

    Sums the fifth column of the range. Basically, it sums the entire right side of the database, from column 5 onward, then subtracts the non-relevant columns from 6 onward.


    PS: I'll be interested in the final, cleaner answer to this, as well.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!