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?


    Cheers,


    Jen

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


    =SUMPRODUCT((OFFSET(Database,0,4,,1)="Barrie")+0,OFFSET(Database,0,6,,1))


    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.


    HTH,
    Chris


    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!

Register Yourself Login