Dragging my formula to the right that goes straight from $C$8:$C$12 to $C$14:$C$18 and so on, without breaking the whole formula

[rogerfoster88]

A huge thanks to Fluff and Jack for helping me get to where i am with my formula.

=SUMIF('[Production Plan.xlsx]Production Plan draft'!$C$8:$C$12,B138,'[Production Plan.xlsx]Production Plan draft'!$D$8:$D$12)/6+SUMIF('[Production Plan.xlsx]Production Plan draft'!$J$8:$J$12,B138,'[Production Plan.xlsx]Production Plan draft'!$K$8:$K$12)/6

This IS WORKING however I can't click and drag to the right and it work for other cells...

Is there a way to have $C$8:$C$12 and $D$8:$D$12 change to $C$14:$C$18 and $D$14:$D$18, and have $J$8:$J$12 and $K$8:$K$12 to change to $J$14:$J$18 and $K$14:$K$18 and have them all continue on that pattern for as long as i drag to the right? Taking the $'s out before the numbers only has the formula change from $C8 to $C9 as you would expect.

Jack mentioned possibly adding to the formula something that tests the row number via IF and applies the above formula if TRUE, but after playing with it over the last 20 hours or so i am having no luck.

Any Help will be greatly appreciated.

 

[rogerfoster88]

The adjusted formula you wrote references $AS8 instead of $AS28 - is this a typo? Given that $AS28 is on the Production Plan draft sheet the formula should still work.

Apologies yes it was.

Fix

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

It's still coming up 0 interestingly enough.

is it possible to share with you this excel sheet here?

 

[tyija1995]

Just to clarify, that formula has been wrote in the Production Plan draft tab and the cell $AS28 is also in that tab? I've tested with some sample data on my end and have some numbers returning with the criteria fulfilled. If your answer is yes to my question then maybe sharing would be a good idea so then I can look further into it and why it's returning a 0 for you.

 

[rogerfoster88]

Just to clarify, that formula has been wrote in the Production Plan draft tab and the cell $AS28 is also in that tab? I've tested with some sample data on my end and have some numbers returning with the criteria fulfilled. If your answer is yes to my question then maybe sharing would be a good idea so then I can look further into it and why it's returning a 0 for you.

Hi Tyija,

Yes to both of those questions.

The formula is written in AT28, AS28 is next to it with the correct name to pick up in the cell range C8:C12 and J8:J12 (also in the same tab).

 

[tyija1995]

Hi Tyija,

Yes to both of those questions.

The formula is written in AT28, AS28 is next to it with the correct name to pick up in the cell range C8:C12 and J8:J12 (also in the same tab).

Okay, at this point I think it's best to see exactly what you're doing in the spreadsheet. I have replicated what I can on my end and this formula is returning correct figures for me, e.g. I have AS10 = 10 and filled some C & J columns with 10 then random figures in D & K to sum. I can't figure out why it's not working for you now unless I can see the sheet.

 

[rogerfoster88]

Okay, at this point I think it's best to see exactly what you're doing in the spreadsheet. I have replicated what I can on my end and this formula is returning correct figures for me, e.g. I have AS10 = 10 and filled some C & J columns with 10 then random figures in D & K to sum. I can't figure out why it's not working for you now unless I can see the sheet.

VERY RANDOM UPDATE

So its working now!!!!!

However...

When i click and drag it to the right its doing the "0" thing again...

AT28 = correct number

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($C$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($J$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(H$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(L$1)-60,COLUMN($K$1))))/6

AU28 = showing "0"

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(I$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(M$1)-60,COLUMN($C$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(I$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(M$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(I$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(M$1)-60,COLUMN($J$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(I$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(M$1)-60,COLUMN($K$1))))/6

AV28 = showing "0"

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(J$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(N$1)-60,COLUMN($C$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(J$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(N$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(J$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(N$1)-60,COLUMN($J$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(J$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(N$1)-60,COLUMN($K$1))))/6

AW28 = showing "0"

=SUMIF(INDIRECT(ADDRESS(6*COLUMN(K$1)-40,COLUMN($C$1))):INDIRECT(ADDRESS(6*COLUMN(O$1)-60,COLUMN($C$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(K$1)-40,COLUMN($D$1))):INDIRECT(ADDRESS(6*COLUMN(O$1)-60,COLUMN($D$1))))/6+SUMIF(INDIRECT(ADDRESS(6*COLUMN(K$1)-40,COLUMN($J$1))):INDIRECT(ADDRESS(6*COLUMN(O$1)-60,COLUMN($J$1))),$AS28,INDIRECT(ADDRESS(6*COLUMN(K$1)-40,COLUMN($K$1))):INDIRECT(ADDRESS(6*COLUMN(O$1)-60,COLUMN($K$1))))/6

And so on.

 

[tyija1995]

Have you compared to a manual SUMIF formula whether you should get 0 or not for C14:C18 etc? It's strange as when I copy across the formula continues to work.

 

[rogerfoster88]

Have you compared to a manual SUMIF formula whether you should get 0 or not for C14:C18 etc? It's strange as when I copy across the formula continues to work.

So i've discovered what the issue is -

Both ways work as they should, but for some reason i need to delete the Data in columns C,D,J,K and re-enter it for the formulas to read it whats in the cells. Why would this be?

 

[tyija1995]

So i've discovered what the issue is -

Both ways work as they should, but for some reason i need to delete the Data in columns C,D,J,K and re-enter it for the formulas to read it whats in the cells. Why would this be?

The only thing I can think of is if your workbook calculation is set to manual, File -> Options -> Formulas -> Calculation options -> Change to Automatic

If this isn't related then I am not sure why you need to refresh the data to get the formulas to work I am afraid!

 

[rogerfoster88]

Tyija1995 you are an absolute genius, it worked! Thanks so much for your persistence with helping me through this.

 

[tyija1995]

No problem, glad to help and good to hear it is working now! Have a good day