Function to sum every n columns

[jeffdolton]

Good morning,

I have a function that sums the value of all products of the same type and returns the result if either chip or contactless payment is made. My OneDrive file is here https://frenshampond-my.sharepoint....1FmAdLZ_bIHt8BPKAPPmLvouhtJBY3vDMg6A?e=Oj9gEN

The functon is:

Function SumIfsEveryNColumns(Cr1 As Variant, Cr2 As Variant, Cr3 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, CrR1 As Range, CrR2 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 CrR1 = Range(ws1.Cells(2, 17), ws1.Cells(Lr1, 17))
Set CrR2 = Range(ws1.Cells(2, 10), ws1.Cells(Lr1, 10))

For j = 19 To Lc Step 5
Set SumRng = Range(ws1.Cells(2, j), ws1.Cells(Lr1, j))
Debug.Print SumRng.Address
P1 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr2)
P2 = Application.WorksheetFunction.SumIfs(SumRng, CrR1, Cr1, CrR2, Cr3)
P = P1 + P2 + P
Next j
Debug.Print P
SumIfsEveryNColumns = P
End Function

However the function isn't working. If you turn to the Summary tab of the workbook and look at cell C20. The function here should return a value of £55 for all boat hires and these are shown highlighted in the Receipts Output tab and the Summary Table tab. What the function is doing is looking for boat hires in column Q only and not all n columns then adding the gross amount from column F where boat hire appears is column Q plus any other product bought with the boat hire. In this case the amount returned in C20 on the Summary tab is £39 - I have highlighted these in column F.

I'd be grateful if this function can be fixed.

Many thanks.




The