CODEWITHSUNDEEP
excelexcel-formuladynamicmodeling
user29643111
user29643111

Reputation: 1

Excel: Repeat Sumifs based on the length of another column

I have created a dynamically updated table where the legnth of criteria in column AL changes. My problem is that the formula in column AN only extends as far as I have it pasted. Is there a way to get my formula to repeat based on the number of rows in column AL? My sumifs will eventually include an offset match and will be completely dynamic (I hope).
enter image description here

I have tried numerous different ways. But none have come close to working. Formula I am looking to repeat lookings some like =SUMIFS('Placeholder'!$M$6:$M$23,OFFSET('Placeholder'!$B$5,1,MATCH($AE$3,'Placeholder'!$C$5:$XFD$5,0),MAX('Placeholder'!$A$6:$A$200*'Placeholder'!$C$6:$C$200)),$AD7)

Upvotes: 0

Views: 57

Answers (1)

JB-007
JB-007

Reputation: 2485

workbook here.

  1. no gaps (sum or criteria cols)*:

.

=SUM(OFFSET(C4,,,COUNTA(C4:C1000))*(OFFSET(D4,,,COUNTA(D4:D1000))=H4))

(insert as array/ctrl+shift+enter if not 365 compatible Excel v.) cells below sum/criteria col data to be clear of obstruction.

no gaps

  1. gaps present**

.

=SUM((C4:OFFSET(C4,MAX(FILTER(SEQUENCE(ROWS(C:C)),--(C:C<>"")))-ROW(C3),))*(E4:OFFSET(E4,MAX(FILTER(SEQUENCE(ROWS(E:E)),--(E:E<>"")))-ROW(E3),)=H5))

gaps present

**requires 365 comp. v. Excel.

Upvotes: 0

Related Questions