SumIfs - Define the Sum_Range by referencing a cell value

  • Hi,


    I have a data dump which has Servers (AMEWEW1ECCS01 being one) as headers and dates in column A. Then values are populated throughout the table for each date in each server.


    This Table is named Oct and each column name is the server name, as in Oct[AMWEW1ECCS01]


    I need to sum all values in the Oct table that are between a date range and for a specific server. This date range is defined by the [To] & [From] columns in another Table. The below works perfectly if I manually tell it which server it is.


    =SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])


    Now I need to make the formula look at a cell in a column called [Server] and populate the Sum_Range accordingly. So replacing the red bit below to reference whatever is in the [Server] column. Any thoughts


    =SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

  • Try this (untested):


    =SUMIFS(INDIRECT("Oct["&A1&"],Oct[Dates]"),">="&[From],Oct[Dates],"<="&[To])


    where A1 contains the server data.

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

  • Hi, thanks


    Nearly there. I tried this and it seems to be failing at the end of the Indirect calculation. I had to add in an extra Oct[Dates] as the rest of the sumif references broke.


    =SUMIFS(INDIRECT("Oct["&[@Server]&"]),Oct[Dates]"),Oct[Dates],">="&[From],Oct[Dates],"<="&[To])


    attached Capture 1 shows it is finding the reference but then it fails to #REF on next step (capture 2)


    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210531}[/ATTACH]


    [ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210532}[/ATTACH]


    any thoughts?

    Images

    • Capture1.PNG
    • Capture2.PNG
  • Try this:


    =SUMIFS(INDIRECT("Oct"&[@Server]&",Oct[Dates]"),Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

Participate now!

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