Column Function to Render Zero

**rentb23** · 11-03-2012, 09:29 AM

Hello

I have this formula entered in a cell

=SUMPRODUCT(($D$4:$BK$4=COLUMN(A$1))*($D34:$BK34))

when i scroll right i get the following formula and so on:
=SUMPRODUCT(($D$4:$BK$4=COLUMN(B$1))*($D34:$BK34))

Column(A$1) renders 1 and Column (B$1) renders 2 and so on

I now introduced column zero and want to scroll left what column function should i use to render zero?

This is what i got when i filled to the left:
=SUMPRODUCT(($D$4:$BK$4=COLUMN(#REF!))*($D34:$BK34))
Many thanks,

**oeldere** · 11-03-2012, 09:33 AM

and what letter has column Zero (letter A)?

**rentb23** · 11-03-2012, 09:46 AM

My Column Zero is in Excel Column C

**oeldere** · 11-03-2012, 09:50 AM

If you have the formula below in column D

=SUMPRODUCT(($D$4:$BK$4=COLUMN(D$1))*($D34:$BK34))

you can pull the formula to the left and you get this formula.

=SUMPRODUCT(($D$4:$BK$4=COLUMN(C$1))*($D34:$BK34))

So i don't get your problem.

There won't be column Zero (because it never exist).

**martindwilson** · 11-03-2012, 09:50 AM

start with =SUMPRODUCT(($D$4:$BK$4=COLUMN(A$1)-1)*($D34:$BK34))

**rentb23** · 11-03-2012, 10:08 AM

Martin's suggestion resolved the problem, I dont have the formula you mentioned in column D it's in a different table and the original range/array is in that table but thanks