Search code examples
exceldynamicsumifs

How to SUMIFS a dynamic column?

I'm using the SUMIFS function in Excel, how do I make the sum range and criteria range to be dynamic?

=SUMIFS(Sheet1!I2:I1040000,Sheet1!J2:J1040000,"PKT*",Sheet1!G2:G1040000,{"BCPF ","EHQ "})

Solution

  • It looks like that formula should have a SUM(...) wrapper to facilitate the OR condition for column G.

    Why do you have trailing spaces in {"BCPF ","EHQ "} ?

    This SUMIFS from row 2 to the row with the last number in column I.

    =SUM(SUMIFS(Sheet1!I2:index(Sheet1!I:I, match(1e99, Sheet1!I:I)),
            Sheet1!J2:index(Sheet1!J:J, match(1e99, Sheet1!I:I)), "PKT*",
            Sheet1!G2:index(Sheet1!G:G, match(1e99, Sheet1!I:I)), {"BCPF ","EHQ "}))

    I'm not sure why this is necessary or even an issue; SUMIFS uses full column range references without calculation penalty.