Return a value in formula so SUMIFS can subtotal

[Jpalaci1]

I have a formula I have a Pivot Table I use to sum data of costs and wish to use those values to forecast months without actuals. Column H is a helper column that helps to identify whether the values is a subtotal (just like I have a subtotal in the Pivot Table). Column Q looks at H and then uses a IFS with a nested SUMIFS to look at I and J to give me a running subtotal for the values I'm forecasting on. 

The formula works and I can copy and paste from it's spot in Q1 down the entire length of the Pivot Table. The formula works for cell Q6 $302,500. I get that by manually typing in what I want to forecast for that particular row. I noticed in Q10 doesn't work because Q7 to Q8 are manually typed values I want to forecast but if I leave Q9 with the zero that results from the formula my subtotal doesn't compute to just add the two I manually added together and take the 0. How can this be fixed? overwriting the 0 from the formula fixes it but I have about 250 rows and the copy paste values onto or typing a value in every cell isn't ideal. Is there any way to fix this?

[#2 · Accepted solution]

@Jpalaci1 Your formula creates circular references. To avoid that , change it so that is sums from the first row of the pivot table to the row direct above the cell where the formula is entered. Don't enter the formula in the very first row. See attached. Now you can start typing the forecast amounts and the sub-totals should calculate correctly. 

[#3]

Thank you so much! This worked very well.