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]

Please upload Example file and desired results with XL2BB ADDIN ot at free hosting site e.g. GoogleDrive, OneDrive or www.dropBox.com and insert link here.

 

[jeffdolton]

Please upload Example file and desired results with XL2BB ADDIN ot at free hosting site e.g. GoogleDrive, OneDrive or www.dropBox.com and insert link here.

Hi maabadi.

It's great that you've replied as you wrote the code for the string separation. Here is the OneDrive link example file with LUp and Totals 07.01.2020 TEST.xlsm You will see that the code works on data that is copied in on the Receipts Output tab up to row L. Your code separates the description string, then further code applies a look up and inserts two columns to include an if then and a sum formula.

What I want to do is sum the Total cost wherever it appears but need to apply a criteria. You will see on the tab Category Totals that I have tried to sum these (I allowed for up to thirty items). I've now deleted the data except for the formula in C5. My comments in red should explain more.

I think solving this by vba code is the answer - to sum every 5th column from a given point to the nth.

Thanks again.

 

[maabadi]

Cell C5 shows desired output. But cell refrences change, as shown below, when code to insert columns is run.

You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.

=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))

to sum every 5th column from a given point to the nth.

Use this formula :

=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))

 

[jeffdolton]

You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.

=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))

Use this formula :

=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))

Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula a try thanks, but would appreciate it if you could advise how I can

You forgot to fix addresses with F4. For don't change Column Letter when formula run at multiple column you should add $ before Column letter e.g. $S4 Not S4
Also for Row Number, You should Add $ before Row number, e.g. S$4 Not S4 , and if you want fix both use $S$4 not S4 (all of this do with pressing F4 repeatedly)
Thus formula is This.

=IF(OR('Receipts Output'!$J:$J="Contactless",'Receipts Output'!$J:$J="Chip"),SUMIFS('Receipts Output'!$S:$S,'Receipts Output'!$Q:$Q,"Kitchen"))

Use this formula :

=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL105)-COLUMN('Receipts Output'!$S2)+1,5)=0)*ISNUMBER('Receipts Output'!$S2:$AL105))

Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula for summing to nth column a try thanks, but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula. Many thanks, Jeff

 

[jeffdolton]

Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula a try thanks, but would appreciate it if you could advise how I can


Yes, I did forget the first two cells but the third I did fix with $ but that changed too. I will give your formula for summing to nth column a try thanks, but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula. Many thanks, Jeff

Don't know what I've done wrong but the formula returns a total of 416 whereas the actual sum of all the 5th columns is 296.10. Any idea why this might be? Thanks, Jeff

 

[maabadi]

but I would appreciate it if you could advise how I can adapt this to sum by the category (kitchen) and payment type (contactless/chip) as shown in the first formula.

=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL100000)-COLUMN('Receipts Output'!$S2)+1,5)=0)*(ISNUMBER('Receipts Output'!$S2:$AL100000))*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))

Are you sure

Don't know what I've done wrong but the formula returns a total of 416 whereas the actual sum of all the 5th columns is 296.10. Any idea why this might be?

 

[jeffdolton]

=SUMPRODUCT(--(MOD(COLUMN('Receipts Output'!$S2:$AL100000)-COLUMN('Receipts Output'!$S2)+1,5)=0)*(ISNUMBER('Receipts Output'!$S2:$AL100000))*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))

Again, many thanks. This time I used the formula it returned a total of 408 whereas the actual figure for all 5th columns from S2 is 192.95.

 

[maabadi]

I find Problem. Use this Both with CTRL+SHIFT+ENTER

=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER($S2:$AR105),$S2:$AR105,0))

AND

=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$S2:$AR100000),'Receipts Output'!$S2:$AR100000,0)*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))

 

[jeffdolton]

I find Problem. Use this Both with CTRL+SHIFT+ENTER

=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER($S2:$AR105),$S2:$AR105,0))

AND

=SUMPRODUCT(--(MOD(COLUMN($S2:$AR2)-COLUMN($S2)+1,5)=1)*IF(ISNUMBER('Receipts Output'!$S2:$AR100000),'Receipts Output'!$S2:$AR100000,0)*('Receipts Output'!$Q2:$Q100000="Kitchen")*OR(('Receipts Output'!$J2:$J100000="Contactless"),('Receipts Output'!$J2:$J100000="Chip")))

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!