Forum Discussion
Formula drag down help
Hi,
I'm trying to see if there is a way I can drag down a formula but miss out certain cells.
So I have 3 separate rows for Forecast Budget and Actuals which all require different formulas of SUMIFS based on the criteria from right columns along same row. The Range for "Actuals" will be from CMO input sheet. The Range for forecasts and budget I haven't added a input sheet yet but plan to do so.
My problem is the sheet is about 11000 rows long so cant do them individually. Is there anyway I can Add formula to each one then drag each one down and it only updates cell based on what is being updated i.e. forecast budget or Actual.
Attached is the file. Any help appreciated.
Something along these lines:
=IFS($E5="Budget",SUMIFS('Budget Input'!$Q:$Q,'Budget Input'!$E:$E,$A5,'Budget Input'!$C:$C,$C5,'Budget Input'!$AJ:$AJ,$D5,'Budget Input'!$AU:$AU,F$2),$E5="Forecast",SUMIFS('Forecast Input'!$Q:$Q,'Forecast Input'!$E:$E,$A5,'Forecast Input'!$C:$C,$C5,'Forecast Input'!$AJ:$AJ,$D5,'Forecast Input'!$AU:$AU,F$2),$E5="Actual",SUMIFS('CMO Input'!$Q:$Q,'CMO Input'!$E:$E,$A5,'CMO Input'!$C:$C,$C5,'CMO Input'!$AJ:$AJ,$D5,'CMO Input'!$AU:$AU,F$2))
Using the actual names of the other input sheets of course.
1 Reply
Something along these lines:
=IFS($E5="Budget",SUMIFS('Budget Input'!$Q:$Q,'Budget Input'!$E:$E,$A5,'Budget Input'!$C:$C,$C5,'Budget Input'!$AJ:$AJ,$D5,'Budget Input'!$AU:$AU,F$2),$E5="Forecast",SUMIFS('Forecast Input'!$Q:$Q,'Forecast Input'!$E:$E,$A5,'Forecast Input'!$C:$C,$C5,'Forecast Input'!$AJ:$AJ,$D5,'Forecast Input'!$AU:$AU,F$2),$E5="Actual",SUMIFS('CMO Input'!$Q:$Q,'CMO Input'!$E:$E,$A5,'CMO Input'!$C:$C,$C5,'CMO Input'!$AJ:$AJ,$D5,'CMO Input'!$AU:$AU,F$2))
Using the actual names of the other input sheets of course.