SumIf Dynamic Range

[Beatrix]

Hi Everyone ,

I need to sum every 9 rows in column E if next cell in column D is equal to "All". Say D16=All then E16= SUM(E7:E15). I don't want to set the formula every 9 rows as it's a long list:wot Any suggestions on how to do that for a dynamic range?:help

I've thought I could use SumIf and Offset together to get it working for a dynamic range but it didn't work:cool:

Cheers
Yeliz

[SamT]

Yeliz,

You can not put the formula in Column E, but you can use this in any other column

=IF(LOWER($D10) = "all",SUM($E2:$E10),"")IF you paste it in any other Row, be sure to adjust the Row numbers before you press Enter.

After that, just fill the column down to the bottom with the formula

[Aflatoon]

In E10:
=IF(D10="all",SUM(E1:E9),"")
and fill down.

[Beatrix]

Thanks very much SamT. It did work in Column E. Just to learn, why you said that I can't put it in column E??



Yeliz,

You can not put the formula in Column E, but you can use this in any other column

=IF(LOWER($D10) = "all",SUM($E2:$E10),"")IF you paste it in any other Row, be sure to adjust the Row numbers before you press Enter.

After that, just fill the column down to the bottom with the formula

[SamT]

I am assuming that you have some values in E1:E9

You said that you have to sum 9 rows whenever D = "all"

Put my formula in E10 and copy it down to the bottom of D so that it checks for D = "all" in every case.

Tell me what happens

[Aflatoon]

BTW you don't need the LOWER function there. String comparisons in formulas are not case sensitive (unless you use EXACT).

[Beatrix]

Many Thanks for your response:cloud9: Yep I need to sum every 9 rows in column E where says "all" in column D. fIRST row is E7 so I put below formula in E16 and it works fine. I can't believe myself that I was looking for a complex solution for this case. Life is simple but I make it complicated sometimes:devil2:



=IF(D16 = "all",SUM(E7:E15), COUNTIFS(Data!$AK$2:$AK$25452,"="&D16,Data!$C$2:$C$25452,"="&B16))




I am assuming that you have some values in E1:E9

You said that you have to sum 9 rows whenever D = "all"

Put my formula in E10 and copy it down to the bottom of D so that it checks for D = "all" in every case.

Tell me what happens

[Beatrix]

Thanks very much Aflatoon. When we say string comparisons, is that "all" in this case??


BTW you don't need the LOWER function there. String comparisons in formulas are not case sensitive (unless you use EXACT).