Sumifs with function within criteria

[nobbir9]

I'm trying to sum a lagging X number of cells in a row based on a reference cell.

I created the formula:
=SUMIFS(E19:K19,E6:K6,"<=H6",E6:K6,">H6-D14")

However, the last term (">H6-D14") seems to make the formula error out. Does the SUMIFS function not allow you to use a function within the criteria?

Thanks for any help.

 

[pedie]

"m still not clear what you're trying to do....

Have you tried something like this?
'=SUMIF(B2:B10,IF(OR(B2:B10<=B2,B2>B2-C2),C2:C10))

 

[Aladin Akyurek]

I'm trying to sum a lagging X number of cells in a row based on a reference cell.

I created the formula:
=SUMIFS(E19:K19,E6:K6,"<=H6",E6:K6,">H6-D14")

However, the last term (">H6-D14") seems to make the formula error out. Does the SUMIFS function not allow you to use a function within the criteria?

Thanks for any help.

Maybe...

=SUMIFS($E$19:$K$19,$E$6:$K$6,"<="&H6,E6:K6,">"&(H6-D14))

Otherwise elaborate a bit more...

 

[nobbir9]

Hmm. That doesn't seem to work either.

Perhaps a better explanation will help:
I have a row listing a number of loans originated each year. I'm trying to calculate how many are still outstanding in each year based on a loan term which is given. For example, a loan type has a two year term, so sum the last two columns, if the loan has a 5 year term, sum the last five columns, etc. But I want to reference these term lengths so they can be changed later.

This Works:
=SUMIFS(E19:K19,E6:K6,"<=4",E6:K6,">2")
This (referencing the same numbers) doesn't:
=SUMIFS(E19:K19,E6:K6,"<=H6",E6:K6,">D14")

 

[Aladin Akyurek]

Hmm. That doesn't seem to work either.

Perhaps a better explanation will help:
I have a row listing a number of loans originated each year. I'm trying to calculate how many are still outstanding in each year based on a loan term which is given. For example, a loan type has a two year term, so sum the last two columns, if the loan has a 5 year term, sum the last five columns, etc. But I want to reference these term lengths so they can be changed later.

This Works:
=SUMIFS(E19:K19,E6:K6,"<=4",E6:K6,">2")
This (referencing the same numbers) doesn't:
=SUMIFS(E19:K19,E6:K6,"<=H6",E6:K6,">D14")

You need to pay closer attention:

=SUMIFS(E19:K19,E6:K6,"<="&H6,E6:K6,">"&D14)

 

[nobbir9]

Aladin! That works! Thanks so much.

It looks like I needed to include quotes around the < signs and a & after each. Per your example: "<="&F$6

Thanks again for your help.

Maybe...

=SUMIFS($E$19:$K$19,$E$6:$K$6,"<="&H6,E6:K6,">"&(H6-D14))

Otherwise elaborate a bit more...