SUMPRODUCT() in XL Table When Dragged Across Columns Loses Reference

[isadoko]

I am using two worksheets. The first to show the results, the second, Table13 contains my data. The formulas below are in the first worksheet and reference data in the second worksheet. The data worksheet is an XL Table.

This is my base formula: =SUMPRODUCT((Table13[d.DESCRIPTION]=$A8)*(Table13[d.CATEGORY]=$B8)*(Table13[d.ACCTDATE]=C$7)*(Table13[AMOUNT])) in columns C-N, ie, Jan-Dec.

When I drag the formula across the columns I noticed the columns referenced in the formula all shifted over one column to the right resulting in the updated and now wrong formula below.

=SUMPRODUCT((Table13[RATE]=$A8)*(Table13[ISSUANCE DATE]=$B8)*(Table13[CUSTOMER ID]=F$7)*(Table13[INVOICE_TYPE]))

Notice d.DESCRIPTION changed to RATE, d.CATEGORY changed to ISSUANCE DATE, d.ACCTDATE changed to CUSTOMER ID and AMOUNT changed to INVOICE_TYPE

I need to keep the original columns references fixed as I drag across to the right. Curiously the cell references, $A8, $B8 and C$ to F$7, in the worksheet behaved as expected.

Thanks in advance for your help.

 

[Alex Blakenburg]

There are 3 common ways of copying the formula across. 2 keep the absolute cell referencing 1 doesn't.
As you have found out the one that doesn't is the drag option. Since it is the only one that treats the referencing as relative it does have its uses.
To keep the original column referencing (absolute) use either of these two methods.

• Use Fill Right
  Select the cell with the formula, the drag to select the cells to the right or shift select.
  Hit Ctrl + R
• Copy Paste
  Standard copy and paste.

If you formula is not inside the table as in your case, you can force it to be absolute but it seems a lot of trouble.

To force it, make each column reference into a 1 column range:-

=SUMPRODUCT((Table13[[d.DESCRIPTION]:[d.DESCRIPTION]]=$A8)*(Table13[[d.CATEGORY]:[d.CATEGORY]]=$B8)*(Table13[[d.ACCTDATE]:[d.ACCTDATE]]=C$7)*(Table13[[AMOUNT]:[AMOUNT]]))