Excel changing cell references when columns inserted - VBA needed

[jeffdolton]

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]

For this formula you should input row numbers also to work (I don't know Why?). Thus:

=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")))

 

[maabadi]

For Product lookup Sheet Use this formula & you can remove columns from E to P

=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]

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

=SumIfsEveryNColumns('Receipts Output'!S2:AR105,'Receipts Output'!Q2:Q105,'Receipts Output'!Q78,'Receipts Output'!J2:J105,'Receipts Output'!J5,'Receipts Output'!J4)

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]

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

=SumIfsEveryNColumns('Receipts Output'!S2:AR105,'Receipts Output'!Q2:Q105,'Receipts Output'!Q78,'Receipts Output'!J2:J105,'Receipts Output'!J5,'Receipts Output'!J4)

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]

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]

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]

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]

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:

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:

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

 

[jeffdolton]

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:

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:

=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]

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.