Sum if in Vba

**buckhunt122** · 07-06-2011, 12:01 PM

Hey another question, from the peskiest forum member

I understand how the sumif command works but I am trying to write that in a macro without using the .formula method (Because the target ranges may vary in size)

I attached a very simple and straitforward spreadsheet. On sheet 1 you will see a range of list boxes, those list boxes are populated by a known list of applications. The user selects an application from the list box, then puts the estimate next to it, and moves down the list. Each application may be used 1, 5, or 10, or maybe not at all. On the summary tab I want to copy over the names of the used applications, (but only 1 name if the application was used multiple times) and then the sum of the estimates for that application. Like is said it sounds like a basic sumif, but I dont really know how to do it, since Im not sure what the range sizes will be, ahead of time.

Thanks, Owen

**buckhunt122** · 07-06-2011, 01:02 PM

Option Explicit

Sub copyover()

Dim r As Range
Dim appname As Range
Dim i As Integer
Dim b As Integer
Dim tally As Range
Dim sum As Range
Dim m As Range
Dim numb As Integer
Dim mark As Integer


mark = Sheets("enter").Range("A3", Range("A3").End(xlDown)).count - 1

Set appname = Sheets("enter").Range("A4", "A23")
Set tally = appname.Offset(0, 1)

For Each r In appname
    i = 1
    
    For i = 1 To mark
              
        If (r.Value <> Sheets("summary").Range("B6").Offset(i, 0).Value And Sheets("summary").Range("B6").Offset(i, 0).Value = False) Then
        Sheets("summary").Range("B6").Offset(i, 0).Value = r.Value
        End If
        
        If r.Value = Sheets("summary").Range("B6").Offset(i, 0).Value Then
        i = mark
        End If
        
    Next i
Next r

  
 Sheets("summary").Select
    Set m = Range("B7").Offset(b, 0)
    Set sum = Range("B7").Offset(b, 1)
    numb = Range("B6", Range("B6").End(xlDown)).count - 1

For b = 0 To numb
    Range(sum).Formula = "=SUMIF(enter!" & appname & ",summary!" & m & ",enter!" & tally & ")"

Next b
        
 
End Sub

It works accept for the Formula line (4 lines up from bottom)

**Marcol** · 07-06-2011, 02:07 PM

Do you really need VBa for this?

Try this workbook.

It uses dynamic named ranges and native formulae

1/. Dynamic Named Ranges
"app_name"
Refers to

=OFFSET(enter!$A$3,0,0,COUNTA(enter!$A:$A)-2,1)

"User_Values"
Refers to

=OFFSET(enter!$B$3,0,0,COUNTA(enter!$A:$A)-2,1)

(You could do the same with your validation list.)

2/. In Sheet "summary" B7

=LOOKUP(REPT("Z",255),CHOOSE({1;2},"",INDEX(app_name,MATCH(TRUE,INDEX(ISNA(MATCH(app_name,$B$6:$B6,0)),0),0))))

And in C7

=IF($B7="","",SUMIF(app_name,$B7,User_Values))

Drag/Fill both down until blank rows are returned, or the same number of rows as are in your validation list and a few more to allow for additional codes.

Add some more data to your table sheet "enter" to see the resullts.

Don't merge cells! They are just a pain, see how your sheet has been formatted to retain the look of merged cells.

There are many advantages to this method rather than using VBa.

These include automatic calculation, ranges can be dragged around the sheet without having to alter any code, sheets can be renamed and the formulae will update, etc .....

Hope this helps.

**buckhunt122** · 07-06-2011, 04:02 PM

I need the code to do it, remember this is only a snippet of what I am actually doing, I just recreated something that I could convet into what I need. Sorry for not being clear, You still get rep, I just need to make myself more clear.

it has to be code because:

the app names are user inputted, not a,b,c,d,e etc
The ranges have unknwon size, and rows may be added (there is other stuff on sheet)
eventually the data will be copied to another workbook

Sorry for not being clear, thanks for the input tho

**Marcol** · 07-07-2011, 07:10 AM

Hmm?

1/.

the app names are user inputted, not a,b,c,d,e etc

Provided that all the app names are text strings then it doesn't matter how they are entered.
The formula in "summary" B2 will return the unique values from the named range "app_name"

You can use a drop-down validation list and allow user entry by doing this.
Data Validation > Select the "Error Alert" tab and uncheck Show error alert after invalid data is entered.

2/.

The ranges have unknwon size, and rows may be added (there is other stuff on sheet)

Again this is taken care of by using the dynamic named ranges, the formulae used to define the names will grow/shrink the list as required.

To make this more versatile than in the first example I posted try the following modifications
Add another name "Header_App"
Refers to:=

=enter!$A$2

Change "app_name"
Refers to:=

=OFFSET(Header_App,1,0,MATCH("*",OFFSET(enter!$A:$A,0,COLUMN(Header_App)-1),-1)-ROW(Header_App),1)

The dimensions of "app_name" are now calculated according to the position of the range "Header_App"

Now you can Drag Columns A & B either singly or together to anywhere on the sheet that you wish and the formulae in sheet "summary" will still return the correct results.

3/.

eventually the data will be copied to another workbook

This is now straightforward just copy and paste > paste special > values then, if required, paste > paste special > formats, to your desired location.

It is always better in my opinion to use native formulae rather than VBa.
Native formulae has many built in features, such as updating when dragged, that are not always easy to simulate with VBa, often as not the code has to be manually amended when changes are made to the sheet, and sooner or later it will fail because the appropriated changes are forgotten.

So in this case why press a button every time a change is made when Excel can do it for you?

See this updated workbook.
Try dragging "enter" A1:A22 or B1:B22 or both to any new position you want.
Try adding a text entry to column A (Numbers will fail if if not entered as text e.g. 123 will fail but '123 will be okay), also don't leave any blanks in this column, the list must be continuous.

Thread: Sum if in Vba

Thread Tools

Display

Sum if in Vba

Re: Sum if in Vba

Re: Sum if in Vba

Re: Sum if in Vba

Re: Sum if in Vba

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions