Reference table headers in conditional sum formula

    Hello. I am new to the forum and looking for help with a formula. I have a dataset formatted as table "Historical" with several columns. I have formulas with structured references, like this: =SUMIF(Historical[2011 Cat.],"Sales",Historical[Feb '11 Sales])


    I would like to instead use a reference to a list that contains all my column headings, something like this:
    =SUMIF(Historical[2011 Cat.],"Sales",Historical[A1])


    I have tried using INDIRECT but doesn't seem to work in this situation. Any ideas? Thanks in advance.

    Re: Reference table headers in conditional sum formula


    If you have the list defined, you just use the list name. Don't understand why you want to use an indirect reference to a list unless there is something else going on. If you have a sample that isn't working, attach that so we can see what you have and also include what it's supposed to look like/do.

    Re: Reference table headers in conditional sum formula


    Thanks for the reply ShosMeister. I've attached an example. Sheet1 is a snapshot of my table "Historical", Sheet2 are the structured formulas. Rather than typing in the table column headings in each formula, I'd like to pull from the Column Lists in columns I-J.

    Re: Reference table headers in conditional sum formula


    Are you using Excel 2007? If so, look at the SUMIFS which is new in 2007. It allows for multiple criteria. You could then change the left heading to the value you want to match similar to what you are doing with the $C$2/$F$2 reference (number and sales).


    Not sure if this will help or not but thought I'd throw it out there.

Participate now!

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

Register Yourself Login