How do I use SUMIF in Excel VB

tommill52

New Member
Joined
May 12, 2015
Messages
27
Hi

I have two worksheets. Worksheet 'detail' contains details of sales transactions (values). Each row shows a Customer code, and transaction values. The transactions appear in multiple columns under different 'groups', as below.


image does not work so please see cut & paste from Excel

Customer10CC10CE10IH10IJ10QE
A01528.60550.8898025.99
A0117.4665.220044.33
A0122.450022.660
B0200393.7200
B020123.7836.0691101.12
B0202214.412900
G02088.344597.003015.15
G0200220.384800
G0200160.71600
G02001173.90300

<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

I need to be able to summarise this data in another worksheet which has the same basic layout, but contains a summary total for each Customer, as below.


image does not work so please see cut & paste from Excel

Customer10CC10CE10IH10IJ10QE
A01568.45665.22550.889822.6670.32
B020145.78444.20201.12
G02088.346152.007015.15


<colgroup><col><col span="5"></colgroup><tbody>
</tbody>

NOTE: this is part of a much larger VB set of codes, and normally includes 50+ customers and around 30 groups, but I am after the principle and thought it best to minimise the examples.

Any help on this would be really appreciated, as I have spent hours looking at code snippets.
 
Last edited:

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
No one can see your images and they definitely can't see anything on your local hard drive. You can upload files to a dropbox account or something like that and share the link or you can read the forum guidelines on ways to show samples of your spreadsheet. https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html

But for this example, I will sum Column A, where Column B is 7:

Code:
Sub test()
MsgBox Application.WorksheetFunction.SumIf(Range("B:B"), 7, Range("A:A"))
End Sub
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
What you want is a Pivot Table.

This one is really easy.

Select your range.
Click on Insert, then Pivot Table
Under Choose where you want the Pivot Table to be placed select New Worksheet
Click OK
On the right under Choose fields to add to the report, select all of them
When changes are made, click in the table, and click on Refresh from the ribbon (alternatively, you could have some VBA code to do the refresh for you)

Then you could do something like this in the Worksheet Change Event (in this example, your Pivot Table is on Sheet5:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet5").PivotTables(1).PivotCache.Refresh
End Sub
 

tommill52

New Member
Joined
May 12, 2015
Messages
27
What you want is a Pivot Table.

This one is really easy.

Select your range.
Click on Insert, then Pivot Table
Under Choose where you want the Pivot Table to be placed select New Worksheet
Click OK
On the right under Choose fields to add to the report, select all of them
When changes are made, click in the table, and click on Refresh from the ribbon (alternatively, you could have some VBA code to do the refresh for you)

Then you could do something like this in the Worksheet Change Event (in this example, your Pivot Table is on Sheet5:

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet5").PivotTables(1).PivotCache.Refresh
End Sub
Hi Scott

Sorry, I should have said - I can get it to work using pivot tables, but the problem is the user wants to be able to 'edit' the results (evidently the stored data is not always assigned correctly). I was looking at a mix of VLOOKUPs and SUMIFs but didn't get very far.
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
If you can make it work with Pivot Tables, then I can make that work for what you need. You may need to change sheet references and Pivot Table references as needed.

Make the Pivot Table.
Hide the sheet that it's on. (In my case, it is Sheet5)
This makes a copy of the data that is in the Pivot Table onto Sheet2, the end user never sees Sheet5 as it is hidden and they can edit the data because they are not actually editing the Pivot Table.

In the Worksheet Change Event code for the sheet you are entering data on:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet5").PivotTables(1).PivotCache.Refresh
Application.ScreenUpdating = False
With Sheets("Sheet2")
    .Cells.ClearContents
    Sheets("Sheet5").Cells.Copy
    .Range("A1").PasteSpecial xlPasteValues
    .Columns.AutoFit
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
 
Last edited:

tommill52

New Member
Joined
May 12, 2015
Messages
27
If you can make it work with Pivot Tables, then I can make that work for what you need. You may need to change sheet references and Pivot Table references as needed.

Make the Pivot Table.
Hide the sheet that it's on. (In my case, it is Sheet5)
This makes a copy of the data that is in the Pivot Table onto Sheet2, the end user never sees Sheet5 as it is hidden and they can edit the data because they are not actually editing the Pivot Table.

In the Worksheet Change Event code for the sheet you are entering data on:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Sheet5").PivotTables(1).PivotCache.Refresh
Application.ScreenUpdating = False
With Sheets("Sheet2")
    .Cells.ClearContents
    Sheets("Sheet5").Cells.Copy
    .Range("A1").PasteSpecial xlPasteValues
    .Columns.AutoFit
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Thanks Scott - I like the idea!

I have created the pivot table but I can't run your code as when I try to, VB does its trick of listing the macros/subs and asks which one I want to run but, for some reason, the worksheet I have stored it behind is not listed, although it does appear in the object browser.

Another thing whilst I think of it: when I tried to do this using a pivot table before, it worked apart from a problem when the macro file was re-run and the source data was re-imported. A lot of the 'columns' (eg 10CC etc) had disappeared. I tried this several times and always had the same result. Can you advise on why this is happening?
 

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,884
It's event code. It needs to be in the Worksheet module for the sheet you enter data on. It will run automatically when any cell is edited on that worksheet. It can be edited to run for a specific range if necessary.
 

tommill52

New Member
Joined
May 12, 2015
Messages
27
It's event code. It needs to be in the Worksheet module for the sheet you enter data on. It will run automatically when any cell is edited on that worksheet. It can be edited to run for a specific range if necessary.
Thanks Scott. I modified your code slightly so that the user can run the code from the destination worksheet from an on-screen button, and it works like a dream.

The problem I mentioned before is still present. The Data Source for the Pivot Table keeps shrinking each time the file is opened and the various bits of VB code are run. The data for the pivot table comes from a sheet that is cleared and repopulated each time the file is opened. The data range for the pivot table starts at 'inr035_temp!$A:$Z' but after a couple of re-runs it has changed to 'inr035_temp!$A:$J', ie it has knocked half of the columns off. Do you know anything about this issue?
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top