Share via

How to get dynamic "SUM range" in sumif formula

Anonymous
2010-07-30T05:33:09+00:00

Hi

I have sheet 1 where i have stored database as shown below illustration:

   A         B          C        D       E       

            Jan        Feb     Mar     Apr

AAA      10          12       15       19

BBB       12          18       20       21

AAA       09         22       11       12

CCC       07         14       18       21

 In Sheet 2

 A1 = Jan

I have formula

=SUMIF(Sheet 1!A2:A5,"AAA",Sheet 1!B2:B5) where the sum range B2:B5 represent the coulumn Jan.

I want a formula where I can change the sum range by just changing the cell reference A1 by "Feb" and hence formula take range C2:C5.

Pls help with this formula if possible.

Hiren

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2010-07-30T06:10:56+00:00

Try this formula:

=SUMIF(Sheet1!A2:A5,"AAA",OFFSET(Sheet1!A2:A5,,MATCH(Sheet1!A1,Sheet1!B1:Z1,0)))

Make sure that the data in your table are numbers and not text. In your example you have 09 and 07 that can be numbers formatted as "00", but it can also be text. If it is text the proposed formula will not work.

Hope this helps / Lars-Åke

Was this answer helpful?

6 people found this answer helpful.
0 comments

Answer accepted by question author

Anonymous
2010-07-30T06:22:03+00:00

Try this is you need to handle numbers formatted as text in you data table:

=SUMPRODUCT(--(Sheet1!A2:A5="AAA"),--OFFSET(Sheet1!A2:A5,,MATCH(Sheet1!A1,Sheet1!B1:Z1,0)))

Change the Z in range B1:Z1 to fit the size of your header row.

Hope this helps / Lars-Åke

Was this answer helpful?

1 person found this answer helpful.
0 comments

7 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-07-30T12:58:11+00:00

    Non volatile formula:

    =SUMIF(A2:A5,"AAA",INDEX(B2:E5,,MATCH(A1,B1:E1,)))

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2010-07-30T06:03:42+00:00

    Steve,

    Thanks for reply.

    I have a big database where i have dates in place of month as shown in above illustration and it imposible to define range name for each dates.

    Hiren

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-07-30T05:54:34+00:00

    Just name each range, select B2:B5 and in the name box type Jan, do this for each range, then change your formula to

    =SUMIF(Sheet 1!A2:A5,"AAA",INDIRECT(A1))

    Regards

     Steve

    Was this answer helpful?

    0 comments