How do I use SUMIF in Excel VB

T

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

[TABLE="width: 387"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]10CC[/TD]
[TD]10CE[/TD]
[TD]10IH[/TD]
[TD]10IJ[/TD]
[TD]10QE[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]528.6[/TD]
[TD]0[/TD]
[TD]550.8898[/TD]
[TD]0[/TD]
[TD]25.99[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]17.4[/TD]
[TD]665.22[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]44.33[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]22.45[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]22.66[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]393.72[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]0[/TD]
[TD]123.78[/TD]
[TD]36.06911[/TD]
[TD]0[/TD]
[TD]1.12[/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]0[/TD]
[TD]22[/TD]
[TD]14.4129[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]G02[/TD]
[TD]0[/TD]
[TD]88.34[/TD]
[TD]4597.003[/TD]
[TD]0[/TD]
[TD]15.15[/TD]
[/TR]
[TR]
[TD]G02[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]220.3848[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]G02[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]160.716[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]G02[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1173.903[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 387"]
<colgroup><col><col span="5"></colgroup><tbody>[TR]
[TD]Customer[/TD]
[TD]10CC[/TD]
[TD]10CE[/TD]
[TD]10IH[/TD]
[TD]10IJ[/TD]
[TD]10QE[/TD]
[/TR]
[TR]
[TD]A01[/TD]
[TD]568.45[/TD]
[TD]665.22[/TD]
[TD]550.8898[/TD]
[TD]22.66[/TD]
[TD]70.32[/TD]
[/TR]
[TR]
[TD]B02[/TD]
[TD]0[/TD]
[TD]145.78[/TD]
[TD]444.202[/TD]
[TD]0[/TD]
[TD]1.12[/TD]
[/TR]
[TR]
[TD]G02[/TD]
[TD]0[/TD]
[TD]88.34[/TD]
[TD]6152.007[/TD]
[TD]0[/TD]
[TD]15.15

[/TD]
[/TR]
</tbody>[/TABLE]

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:
Sort by date Sort by votes
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
 
Upvote 0
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
 
Upvote 0
Scott Huish said:
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
Click to expand...

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.
 
Upvote 0
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:
Upvote 0
Scott Huish said:
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
Click to expand...

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?
 
Upvote 0
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.
 
Upvote 0
Scott Huish said:
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.
Click to expand...

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

Similar threads

M
  • Question Question
How do I save a CSV file (Comma Separated Value) with VBA in Excel 2000?
Replies
5
Views
382
motilulla
M
K
  • Question Question
Creating a Pivot Table from a complex Excel sheet, how do I get this to work?
Replies
1
Views
601
Meggesto
M
J
  • Question Question
How do I count unique text occurences based on a single criteria where some of the cells are #N/A
Replies
3
Views
447
Aladin Akyurek
Aladin Akyurek
S
  • Question Question
Trying to use a duration format as numbers in Excel 2013.
Replies
1
Views
308
PaddyD
P
C
  • Question Question
How do I extract numbers from a data list that also contains text - using a formula
Replies
8
Views
597
Ingolf
I

Forum statistics

Threads
1,226,840
Messages
6,193,283
Members
453,788
Latest member
drcharle

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