Prevent certain values from being changed when using autofill

Hi,

I am working on a spreadsheet that requires addition of certain cells only if the value of another cell is greater than or equal to that of the value of another cell. The example below shows the formula as it pertains to the first row:

=SUM(262+171+31)+(IF(F23>=F11,F11,0))+(IF(H23>=H11,H11,0))+IF(J23>=J11,J11,0)+IF(K23>=K11,K11,0)+IF(L23>=L11,L11,0)+IF(M23>=M11,M11,0)+IF(N23>=N11,N11,0)

So, basically, if the value of F23 is greater or equal to F11, then I want to add F11. Ergo, if F23 is less than F11, I don't want it to be included. The same goes for H, I, J and so forth.

I need the values of the subsequent cells in a series, say, F24, F25, etc., to only be added if they >= F11. What the autofill is doing is advancing the cells from F23 to F24, F25, and so on, (which is what I want), but it's also advancing the cells starting at F11, so it looks like this:

=SUM(262+171+31)+(IF(F24>=F12,F12,0))+(IF(H24>=H12,H12,0))+IF(J24>=J12,J12,0)+IF(K24>=K12,K12,0)+IF(L24>=L12,L12,0)+IF(M24>=M12,M12,0)+IF(N24>=N12,N12,0)

I need the formula above to read:

=SUM(262+171+31)+(IF(F24>=F11,F11,0))+(IF(H24>=H11,H11,0))+IF(J24>=J11,J11,0)+IF(K24>=K11,K11,0)+IF(L24>=L11,L11,0)+IF(M24>=M11,M11,0)+IF(N24>=N11,N11,0)



I have about 35 to 40 rows to complete, so manually adjusting those formulas will take some time. I'm hoping there's a way for Excel to recognize the pattern (that row 11 remains constant, while rows 23 onward are advanced by one).

Hi & welcome to the board
try

Formula:

Please Login or Register  to view this content.

Worked perfectly, thank you so much!

You're welcome & thanks for the feedback