SUMIFS formula w/ variable Sum_Range

    Hi,


    I was wondering if someone can help me with a SUMIFS formula. The following is what I currently have:


    =SUMIFS('Preventive Maintinance Report '!$AX:$AX,'Preventive Maintinance Report '!$A:$A,"P2",'Preventive Maintinance Report '!$D:$D,'Maintenance Hrs Rprt Drill Down'!$A11)


    Instead of defining the sum range as 'Preventive Maintinance Report '!$AX:$AX, I would like to do a lookup based on column heading. For example, if row 3 containing headers has machine C1, C2, Total, assume Column AX is total. I want to specify in a cell above my report to pull "C1" data and have the formula update to change the range.


    I looked at a few examples online with SUMIF formula but it didn't help. I would really appreciate it if someone could help me out. Thanks.

    Re: SUMIFS formula w/ variable Sum_Range


    one approach is to combine INDIRECT and ADDRESS function to generate the range address..the ADDRESS function will be used to generate the range address and INDIRECT function will enable its use in the SUMIFS formula....

    Re: SUMIFS formula w/ variable Sum_Range


    Another approach is to define names for the columns you want:
    e.g.
    Name Refers To
    MachineC1 Preventive Maintinance Report'!$AV:$AV
    MachineC2 Preventive Maintinance Report'!$AW:$AW
    Total Preventive Maintinance Report'!$AX:$AX


    Then your formula will be:
    SUMIFS(INDIRECT(Cell with the value),'Preventive Maintinance Report '!$A:$A,"P2",'Preventive Maintinance Report '!$D:$D,'Maintenance Hrs Rprt Drill Down'!$A11)

    Re: SUMIFS formula w/ variable Sum_Range


    Please see attached.
    The only problem is that you cannot create a Named Range "C" so I adjusted your list, instead of C & D, I changed it to "PrinterC" and "PrinterD".
    It works but with this slight adjustment. If you do not want to adjust your workbook maybe pangolin can show both of us how to combine INDIRECT and ADDRESS without using Named Ranges.


    Best regards

    Re: SUMIFS formula w/ variable Sum_Range


    You can simply match B15 against the headers in E3 and F3 (on Data sheet). No need for any other table listing the ranges. Try this formula


    =SUMIFS(INDEX(Data!$G:$H,0,MATCH($B$15,Data!$E$3:$F$3,0)),Data!$A:$A,"P2",Data!$D:$D,$A18)


    It's usually preferable to avoid volatile functions like INDIRECT where possible.......


    regards, daddylonglegs

    Re: SUMIFS formula w/ variable Sum_Range


    I don't have any experience using Index & Match unfortunately. I'll try the formula the other way as well.. Thanks!

Participate now!

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

Register Yourself Login