# Absolute cell reference formula help

#### johnny5

##### New Member
I have a spreadsheet that calculates count and sales by item for a 12 month rolling period. I've been updating the formula everytime I add or delete a month to create a new 12 month period. I need a formula to sum the same cell references for each item and not be affected by the addition or deletion of columns. I tried the INDIRECT function but I cannot figure out the correct syntax because I am not using a constant range. Any help would be appreciated.
Book1
ABCDEFGHIJKLM
1ItemJan-05Jan-05Feb-05Feb-05Mar-05Mar-05Apr-05Apr-05May-05May-05TTLTTL
2NumCountSalesCountSalesCountSalesCountSalesCountSalesCountSales
3MX-123223\$8,920.00193\$7,720.00273\$10,920.00217\$8,680.00225\$9,000.00
4TL-500117\$4,680.0086\$3,440.00115\$4,600.00125\$5,000.00111\$4,440.00
5ES-445441\$17,640.00413\$16,520.00462\$18,480.00380\$15,200.00290\$11,600.00
6MT-600621\$24,840.00561\$22,440.00600\$24,000.00580\$23,200.00582\$23,280.00
Sheet1

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### want2b

##### Board Regular
The absolute cell reference won't work?
You probably blew right past me but
I would think that =\$C\$3 in cell AC3 would equal \$8920
until you deleted colums C and D then it
would equal \$7720 in cell AA3

#### joelnichols

##### Active Member
Would something like this work?
Book12
ABCDEFGHIJKLM
2PartCountSales
3TL-500554221201174680863440115460012550001114400
Sheet1

In B3 =SUM(INDEX(3:3,,4))+SUM(INDEX(3:3,,6))+SUM(INDEX(3:3,,8))+SUM(INDEX(3:3,,10))+SUM(INDEX(3:3,,12))

In B4 =SUM(INDEX(3:3,,5))+SUM(INDEX(3:3,,7))+SUM(INDEX(3:3,,9))+SUM(INDEX(3:3,,11))+SUM(INDEX(3:3,,13))

Ugly and probably not the best way to do this but...

#### joelnichols

##### Active Member
Got it looking a little better here

=SUM(INDEX(3:3,,4),INDEX(3:3,,6),INDEX(3:3,,8),INDEX(3:3,,10),INDEX(3:3,,12))

#### johnny5

##### New Member
Sum / Index works great for this application.

Replies
1
Views
282
Replies
1
Views
357
Replies
2
Views
160
Replies
0
Views
32
Replies
3
Views
365