CODEWITHSUNDEEP
google-sheetsarray-formulaslinechartsumifs
M.Zubair Akram
M.Zubair Akram

Reputation: 121

Array Formula alternative solution with SumIF - Google Sheets

I have data in the below format: enter image description here

The "Date" Column is a sequence of dates generated using the formula:

=sequence(datedif(TODAY(),MAX(D2:D),"D")+2,1,TODAY(),Date(0,1,0))

The "Avg. Mnthly Hrs" is calculated against each Date value as below:

=SUMIF(D:D,">="&E2,C:C)

The issue here is, whenever I have a new Project, where the "Exp. Project End Date" is let's say 5/31/2045, the Date column is extended automatically using the sequence formula and generates values in days until that day but the "Avg. Mnthly Hrs" formula doesn't calculate the new values (like in Array Formula). enter image description here

The ArrayFormula doesn't work with SumIF formula. Is there any similar alternative so that the "Avg. Monthly Hrs" formula extends automatically for new values in Date column.

Upvotes: 0

Views: 872

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

In F2, try

=arrayformula(if(E2:E="",,SUMIF(D:D,">="&E2:E,C:C)))

Upvotes: 1

Related Questions