VBA SUMIF Criteria/Ranges in different sheets

W

Werra2006

New Member
Joined
Jan 23, 2013
Messages
11
Hello


I need to modify my code to do a summary in the data capture worksheet of totals based on matching lookup ref for each business area.

Thank you in advance








Raw Data
Lookup Ref</SPAN>
Report Date</SPAN>
Account</SPAN>
Name</SPAN>
Customer
Group</SPAN>
Legal
Entity</SPAN>
Business Unit</SPAN>
Roll Up</SPAN>
Business Area</SPAN>
Contract Region</SPAN>
Direct Responsible
Individual</SPAN>
Cost Centre</SPAN>
Cost Centre Desc.</SPAN>
Activity</SPAN>
Tran
Type</SPAN>
Tran
Ref</SPAN>
Voucher
Number</SPAN>
Credit Limit</SPAN>
Terms Of
Payment</SPAN>
Date</SPAN>
Due
Date</SPAN>
Ageing
Days</SPAN>
Inv / CN
CC = LE
Indicator</SPAN>
Net</SPAN>
VAT</SPAN>
Gross</SPAN>
Part
Settlements</SPAN>
Unallocated
Cash</SPAN>
Balance</SPAN>
0-30</SPAN>
31-60</SPAN>
61-90</SPAN>
91-120</SPAN>
121-150</SPAN>
151-180</SPAN>
181-210</SPAN>
211-240</SPAN>
241-270</SPAN>
271-300</SPAN>
301-330</SPAN>
331-360</SPAN>
360+</SPAN>
30+</SPAN>
60+</SPAN>
90+</SPAN>
120 +</SPAN>
150+</SPAN>
180+</SPAN>
28/02/13</SPAN>
.Ageing</SPAN>
Ageing</SPAN>
Net
Provision</SPAN>
3 - TFM/Security 41333</SPAN>
3 - TFM/Security</SPAN>
0</SPAN>
17,038.84</SPAN>
3,407.77</SPAN>
20,446.61</SPAN>
0.00</SPAN>
0.00</SPAN>
20,446.61</SPAN>
20,446.61</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0</SPAN>
0-30</SPAN>
0-30</SPAN>
0.00</SPAN>
3 - TFM/Security 41333</SPAN>
3 - TFM/Security</SPAN>
28</SPAN>
17,038.84</SPAN>
3,407.77</SPAN>
20,446.61</SPAN>
0.00</SPAN>
0.00</SPAN>
20,446.61</SPAN>
20,446.61</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
28</SPAN>
0-30</SPAN>
0-30</SPAN>
0.00</SPAN>
3 - TFM/Security 41333</SPAN>
3 - TFM/Security</SPAN>
59</SPAN>
15,188.07</SPAN>
3,037.61</SPAN>
18,225.68</SPAN>
0.00</SPAN>
0.00</SPAN>
18,225.68</SPAN>
0.00</SPAN>
18,225.68</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
18,225.68</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
0.00</SPAN>
59</SPAN>
31-60</SPAN>
31-60</SPAN>
0.00</SPAN>

<TBODY>
</TBODY>

Data Capture

Lookup ref</SPAN>1 - National Accounts</SPAN> 2 - Cleaning</SPAN>Lookup ref</SPAN>3 - TFM/SECURITY</SPAN>Lookup ref</SPAN>4 - Historic Contracts</SPAN>Lookup ref</SPAN>5 - BUOHEAD (Commercial)</SPAN>
Total Debt</SPAN>DSO</SPAN>90+</SPAN>60+</SPAN>Total Debt</SPAN>DSO</SPAN>90+</SPAN>60+</SPAN>Total Debt</SPAN>DSO</SPAN>90+</SPAN>60+</SPAN>Total Debt</SPAN>DSO</SPAN>90+</SPAN>60+</SPAN>Total Debt</SPAN>DSO</SPAN>
1 - National Accounts 41305</SPAN> 2 - Cleaning 41305</SPAN> 3 - TFM/SECURITY 41305</SPAN> 4 - Historic Contracts 41305</SPAN> 5 - BUOHEAD (Commercial) 41305</SPAN>
1 - National Accounts 41333</SPAN> £8,780,750</SPAN> 2 - Cleaning 41333</SPAN>£129,263</SPAN>£294,613</SPAN>£9,566,292</SPAN> 3 - TFM/SECURITY 41333</SPAN>£11,525</SPAN>£79,921</SPAN>£8,509,554</SPAN> 4 - Historic Contracts 41333</SPAN>-£2,736</SPAN>-£2,736</SPAN>£2,671</SPAN> 5 - BUOHEAD (Commercial) 41333</SPAN>-£52,113</SPAN>-£52,509</SPAN>-£65,480</SPAN>
1 - National Accounts 41364</SPAN> 2 - Cleaning 41364</SPAN> 3 - TFM/SECURITY 41364</SPAN> 4 - Historic Contracts 41364</SPAN> 5 - BUOHEAD (Commercial) 41364</SPAN>
1 - National Accounts 41394</SPAN> 2 - Cleaning 41394</SPAN> 3 - TFM/SECURITY 41394</SPAN> 4 - Historic Contracts 41394</SPAN> 5 - BUOHEAD (Commercial) 41394</SPAN>
1 - National Accounts 41425</SPAN> 2 - Cleaning 41425</SPAN> 3 - TFM/SECURITY 41425</SPAN> 4 - Historic Contracts 41425</SPAN> 5 - BUOHEAD (Commercial) 41425</SPAN>
1 - National Accounts 41455</SPAN> 2 - Cleaning 41455</SPAN> 3 - TFM/SECURITY 41455</SPAN> 4 - Historic Contracts 41455</SPAN> 5 - BUOHEAD (Commercial) 41455</SPAN>
1 - National Accounts 41486</SPAN> 2 - Cleaning 41486</SPAN> 3 - TFM/SECURITY 41486</SPAN> 4 - Historic Contracts 41486</SPAN> 5 - BUOHEAD (Commercial) 41486</SPAN>
1 - National Accounts 41517</SPAN> 2 - Cleaning 41517</SPAN> 3 - TFM/SECURITY 41517</SPAN> 4 - Historic Contracts 41517</SPAN> 5 - BUOHEAD (Commercial) 41517</SPAN>
1 - National Accounts 41547</SPAN> 2 - Cleaning 41547</SPAN> 3 - TFM/SECURITY 41547</SPAN> 4 - Historic Contracts 41547</SPAN> 5 - BUOHEAD (Commercial) 41547</SPAN>
1 - National Accounts 41578</SPAN> 2 - Cleaning 41578</SPAN> 3 - TFM/SECURITY 41578</SPAN> 4 - Historic Contracts 41578</SPAN> 5 - BUOHEAD (Commercial) 41578</SPAN>
1 - National Accounts 41608</SPAN> 2 - Cleaning 41608</SPAN> 3 - TFM/SECURITY 41608</SPAN> 4 - Historic Contracts 41608</SPAN> 5 - BUOHEAD (Commercial) 41608</SPAN>
1 - National Accounts 41639</SPAN> 2 - Cleaning 41639</SPAN> 3 - TFM/SECURITY 41639</SPAN> 4 - Historic Contracts 41639</SPAN> 5 - BUOHEAD (Commercial) 41639</SPAN>

<TBODY>
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL><COL><COL><COL><COL><COL span=2><COL><COL><COL><COL span=2><COL><COL></COLGROUP>








Code: 
Sub CustomSumIf()
Dim lastGrpRow, grpNum As Integer
Dim sumGrp As Long
Dim firstAddress As String
Dim g As Range
 lastGrpRow = Sheets("DATA Capture").Range("B" & Rows.Count).End(xlUp).Row
   For grpNum = 5 To lastGrpRow
     With Sheets("Raw Data").Columns("A")
      Set g = .Find(Sheets("Data Capture").Range("B" & grpNum), lookat:=xlWhole)
        If Not g Is Nothing Then
          firstAddress = g.Address
            Do
              sumGrp = sumGrp + Sheets("Raw data").Range("AS" & g.Row)
              Set g = .FindNext(g)
            Loop While Not g Is Nothing And g.Address <> firstAddress
        End If
       Sheets("Data Capture").Range("c" & grpNum) = sumGrp
       sumGrp = 0
     End With
   Next
End Sub
 

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.
You must log in or register to reply here.

Similar threads

S
  • Question
Need Help in Dates
2 3
Replies
27
Views
970
Dave Patton
D
M
  • Question
VBA SUMIF with changing ranges and criteria based on string
Replies
3
Views
606
Marcelo Branco
M
J
  • Question
Line too long - How to separate it in 2 or more different lines?
Replies
6
Views
423
Jonmo1
Jonmo1
J
  • Question
Extreme Lag!
Replies
8
Views
802
shg
shg

Forum statistics

Threads
1,215,151
Messages
6,123,316
Members
449,094
Latest member
Chestertim

Share this page

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
Back
Top