Make YTD formula dynamic.

[ammartino44]

Hello. This is a YTD formula.SUMIFS(Sheet1!$D3:$O3,Sheet1!$D$2:$O$2,">="&Sheet2!C$3,Sheet1!$D$2:$O$2,"<="&Sheet2!D$3) How can I make it dynamic so that it looks in a row based on a lookup value. In this if I drag the formula down it will look in the next row, but I want it to specific rows based on specific values.

[cbatrody]

Hi,

I have updated the formula for the Month & YTD in the attached file. Please see if it suits your requirement.


MONTH: INDEX(Sheet1!$C$2:$O$5,MATCH(Sheet2!$B4,Sheet1!$C$2:$C$5,0),MATCH(Sheet2!C$3,Sheet1!$C$2:$O$2,0))


YTD: SUM(INDEX(Sheet1!$C$2:$O$5,MATCH($B4,Sheet1!$C$2:$C$5,0),2):INDEX(Sheet1!$C$2:$O$5,MATCH($B4,Sheet1!$C$2:$C$5,0),MATCH(D$3,Sheet1!$C$2:$O$2,0)))

[ammartino44]

Thanks. Is there a way that you can modify this YTD formula to be dynamic? SUMIFS(Sheet1!$D3:$O3,Sheet1!$D$2:$O$2,">="&Sheet2!C$3,Sheet1!$D$2:$O$2,"<="&Sheet2!D$3)

The reason I like it is because it's actually more than a YTD...It lets you pick the ranges (Feb-April) for example.

[bebo021999]

For month, cell C4:

Please Login or Register  to view this content.

For YTD, cell D4:

Please Login or Register  to view this content.

[cbatrody]

Hi,

I have modified the formula in the attached file.