dynamic sumifs

[daveyc18]

so the formula is at the bottom of a column, but the number of rows can change ...is there a way to make it dynamic so i dont need to adjust every day?

i know ideally the formula should be "off to the side" so it can reference the entire column, but boss doesnt want that

 

[Skyybot]

Format your data as Table and click Total Row under Table Design. Change the formula in the Total cell.

 

[Peter_SSs]

A changed layout would be my recommendation too. Another option would be to have the formulas above the data rather than below.
However, if the formula must go at the bottom, here are two options. I would normally recommend against the OFFSET option as that is a volatile function
If there is not too many volatile formulas in the worksheet it should not be a problem though.

24 11 17.xlsm
	B	C	D
1		Numbers	Numbers
2	a	1	1
3	b	2	2
4	c	3	3
5	a	4	4
6		5	5
Bottom Formula

Cell Formulas
Range	Formula
C6	C6	=SUMIFS(C$2:INDEX(C:C,ROW()-1),B$2:INDEX(B:B,ROW()-1),"a")
D6	D6	=SUMIFS(D$2:OFFSET(D6,-1,0),B$2:OFFSET(B6,-1,0),"a")

 

[daveyc18]

A changed layout would be my recommendation too. Another option would be to have the formulas above the data rather than below.
However, if the formula must go at the bottom, here are two options. I would normally recommend against the OFFSET option as that is a volatile function
If there is not too many volatile formulas in the worksheet it should not be a problem though.

24 11 17.xlsm
	B	C	D
1		Numbers	Numbers
2	a	1	1
3	b	2	2
4	c	3	3
5	a	4	4
6		5	5
Bottom Formula

Cell Formulas
Range	Formula
C6	C6	=SUMIFS(C$2:INDEX(C:C,ROW()-1),B$2:INDEX(B:B,ROW()-1),"a")
D6	D6	=SUMIFS(D$2:OFFSET(D6,-1,0),B$2:OFFSET(B6,-1,0),"a")

great ..is index a volatile function? i don't think it is, but i think i read that it is , according to some

 

[Peter_SSs]

is index a volatile function?

That is discussed in the link that I provided in my previous post.