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

isadoko

Active Member
Joined
Jan 10, 2005
Messages
318
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.
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
626
Office Version
  1. 365
Platform
  1. Windows
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:-

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

Watch MrExcel Video

Forum statistics

Threads
1,130,292
Messages
5,641,385
Members
417,207
Latest member
Vxhaet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top