Excel changing cell references when columns inserted - VBA needed

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Hello, I don't think the answer is as straightforward as the question implies! I have a workbook with four worksheets. In Sheet1 I copy in data from an EPOS system and then I run code provided by members of this Forum that firstly splits a string that contains different products purchased, then code applies a lookup to obtain price and category and finally code inserts two columns and totals the price for each product.

On Sheet 2, I have attempted to sum the total price of each category in Sheet 1 - I tried to obtain a formula to sum every 5th column to the nth, but without success.

The problem is that when the third code is run the cell references in the sumifs formula on Sheet 2 changes even though I have accounted for the insertion of columns and some cell references are locked by using $$. I'm very confused.

Hope you can help - I think this probably needs code to sum the categories.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For this formula you should input row numbers also to work (I don't know Why?). Thus:
Excel Formula:
=SUMPRODUCT(--(MOD(COLUMN($S2:$CC2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$S2:$CC100000),'Receipts Output'!$S2:$CC100000,0)*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
For Product lookup Sheet Use this formula & you can remove columns from E to P
Excel Formula:
=CONCATENATE(A2,IF(B2="",""," ("&B2),IF(C2="","",IF(B2=""," ("&C2,", "&C2)),IF(AND(D2="",B2="",C2=""),"",IF(D2="",")",", "&D2&")")))

But you should change fill Lookup macro also.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
The sumproduct formula is heavy, I write a function to do its work
Example:
1. SumRange 2. Criteria rang1 3. Criteria 1 4. Criteria range 2 5. Criteia 2 6. Criteria 3
Excel Formula:
=SumIfsEveryNColumns('Receipts Output'!S2:AR105,'Receipts Output'!Q2:Q105,'Receipts Output'!Q78,'Receipts Output'!J2:J105,'Receipts Output'!J5,'Receipts Output'!J4)

VBA Code:
Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
', SumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, CrRng4 As Range, Cr4 As Range
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column

'Set CrRng1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
'Debug.Print CrRng1.Address
'Set Cr1 = ws1.Cells(4, 17)
'Set CrRng2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))
'Debug.Print CrRng2.Address
'Set Cr2 = ws1.Cells(4, 10)
'Set Cr3 = ws1.Cells(5, 10)
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr3)
P = P1 + P2 + P

', CrRng3, Cr3, CrRng4, Cr4, CrRng5, Cr5)
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
The sumproduct formula is heavy, I write a function to do its work
Example:
1. SumRange 2. Criteria rang1 3. Criteria 1 4. Criteria range 2 5. Criteia 2 6. Criteria 3
Excel Formula:
=SumIfsEveryNColumns('Receipts Output'!S2:AR105,'Receipts Output'!Q2:Q105,'Receipts Output'!Q78,'Receipts Output'!J2:J105,'Receipts Output'!J5,'Receipts Output'!J4)

VBA Code:
Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
', SumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, CrRng4 As Range, Cr4 As Range
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column

'Set CrRng1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
'Debug.Print CrRng1.Address
'Set Cr1 = ws1.Cells(4, 17)
'Set CrRng2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))
'Debug.Print CrRng2.Address
'Set Cr2 = ws1.Cells(4, 10)
'Set Cr3 = ws1.Cells(5, 10)
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr3)
P = P1 + P2 + P

', CrRng3, Cr3, CrRng4, Cr4, CrRng5, Cr5)
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function
Many thanks. This is what I get when I use the function =SumIfsEveryNColumns( and pressing enter gives #VALUE. I don't know much about vba but it seems there are many comments (in green) in the code and just wondered if this should be part of the code. Also I can't see the start point to apply the formula, that is cell S2 on the Receipts Output. I think this function, when working, will give a total figure for all 5th columns. Can you revise please to include the criteria i.e. category and payment type. Thanks for your commitment to my project, it's much appreciated.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

You should input your arguments like function on it. I gave you one Example.
After =SumIfsEveryNColumns( Press Fx at formula bar
Argument1 , you can select first cell of sum range or total (no different) S2 or S2:AR10000
Argument2 , Criteria rang1 you can select first criteria range
Argument3 , Cr1 you can select first criteria Cell
Argument4 , Criteria rang2 you can select 2nd criteria range
Argument5 , Cr2 you can select 2nd criteria Cell (first OR option = Contactless)
Argument6 , Cr3 you can select 2nd criteria Cell (2nd OR option = Contactless)

Check again my example at before post
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
That works, thanks. I changed the range from AR to CC and got #N/A. I may have more columns beyond AR (your earlier code split the string for up to 50 product items). Please advise how I should change this. Thanks again for all you are doing for me but we're nearly there!
Hi, I also used the sum product formula above that you kindly provided. This provides the correct result but when I delete the Receipts Output data and copy in new data the formula changes as follows: =SUMPRODUCT(--(MOD(COLUMN('Category Totals'!$S2:$CC2)-COLUMN('Category Totals'!$S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$U2:$CE100000),'Receipts Output'!$U2:$CE100000,0)*('Receipts Output'!$S2:$S100000="Kitchen")*OR(('Receipts Output'!$L2:$L100000="Contactless"),('Receipts Output'!$L2:$L100000="Chip"))). You will see that the CC address has changed to CE. This was the problem I got first of all and why I started this thread.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

You should input your arguments like function on it. I gave you one Example.
After =SumIfsEveryNColumns( Press Fx at formula bar
Argument1 , you can select first cell of sum range or total (no different) S2 or S2:AR10000
Argument2 , Criteria rang1 you can select first criteria range
Argument3 , Cr1 you can select first criteria Cell
Argument4 , Criteria rang2 you can select 2nd criteria range
Argument5 , Cr2 you can select 2nd criteria Cell (first OR option = Contactless)
Argument6 , Cr3 you can select 2nd criteria Cell (2nd OR option = Contactless)

Check again my example at before post
I'm sorry you've been so good to me but I don't understand how to do this. Would you kindly update the code below to show criteria for Kitchen and Chip or Contactless. I will then understand and can add in the other criteria.

Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
', SumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, CrRng4 As Range, Cr4 As Range
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column

'Set CrRng1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
'Debug.Print CrRng1.Address
'Set Cr1 = ws1.Cells(4, 17)
'Set CrRng2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))
'Debug.Print CrRng2.Address
'Set Cr2 = ws1.Cells(4, 10)
'Set Cr3 = ws1.Cells(5, 10)
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr3)
P = P1 + P2 + P

', CrRng3, Cr3, CrRng4, Cr4, CrRng5, Cr5)
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
1,354
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
but when I delete the Receipts Output data and copy in new data
When you Delete, I think you Delete Column completely and then Source Changes, for Solving this, Select Column and Select Clear all from Home Tab
Or first Insert your New Data , Then Delete previous Data

This is Previous code only I deleted Green Part. But you can also input names at criteria also range:
VBA Code:
Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr3)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

Example:
Excel Formula:
=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2:$Q$105,"kitchen",'Receipts Output'!$J$2:$J$105,"Contactless","Chip")
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
When you Delete, I think you Delete Column completely and then Source Changes, for Solving this, Select Column and Select Clear all from Home Tab
Or first Insert your New Data , Then Delete previous Data

This is Previous code only I deleted Green Part. But you can also input names at criteria also range:
VBA Code:
Function SumIfsEveryNColumns(FirstCellSumRng As Range, CrRng1 As Range, Cr1 As Variant, CrRng2 As Range, Cr2 As Variant, Cr3 As Variant, Optional CrRng4 As Range, Optional Cr4 As Variant) As Double
Dim i As Long, j As Long, Lr1 As Long, Lc As Long, ws1 As Worksheet, ws2 As Worksheet
Dim P1 As Double, P2 As Double, P As Double, SumRng As Range
Set ws1 = Sheets("Receipts Output")
Lr1 = ws1.Cells(Rows.Count, 1).End(xlUp).Row
Lc = ws1.Cells(2, Columns.Count).End(xlToLeft).Column
For j = FirstCellSumRng.Column To Lc Step 5
Set SumRng = Range(ws1.Cells(FirstCellSumRng.Row, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrRng1, Cr1, CrRng2, Cr3)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

Example:
Excel Formula:
=SumIfsEveryNColumns('Receipts Output'!$S$2,'Receipts Output'!$Q$2:$Q$105,"kitchen",'Receipts Output'!$J$2:$J$105,"Contactless","Chip")
Thanks but I don't know enough about vba to do this. Where does the first cell S2 go? Also I don't know how to input a range - this is what I got - P1 = Application.WorksheetFunction.SumIfs(Q2:Q105, "Kitchen", Cr1, CrRng2, Cr2) which of course is wrong. Can you complete the above fully for me please so I can see how to do it. Can you also advise why you've used Q105 as a range; will this still work with say cc10000? Thanks for your perseverance.
 

jeffdolton

Board Regular
Joined
Dec 21, 2020
Messages
69
Office Version
  1. 2010
Platform
  1. Windows
Thanks but I don't know enough about vba to do this. Where does the first cell S2 go? Also I don't know how to input a range - this is what I got - P1 = Application.WorksheetFunction.SumIfs(Q2:Q105, "Kitchen", Cr1, CrRng2, Cr2) which of course is wrong. Can you complete the above fully for me please so I can see how to do it. Can you also advise why you've used Q105 as a range; will this still work with say cc10000? Thanks for your perseverance.
Hello again! whatever I do, clear all, clear contents etc. the sum product cell address changes even when it is locked with $$. I think a vba function is the only solution.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

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