Solved: Sum If funtion help

[lanhao]

Hi there,

Been struggling with this one.

How would I put in the SUMIF function in as an Application.WorksheetFunction format in VBA?

Any help on this would be appreciated on this.

[lucas]

Why would you desire to do that? Every time you change something in the sheet you will trigger the function......
you can add a formula to a cell like this:
[vba]
.Formula = "=IF(D2=.........)"
[/vba]

[lanhao]

I was looking to get some conditional formatting done in the cells as well, adn it won't do that with the way the formula is in there presently. I have tried it that way with the quotations and would rather have it streamline a bit more, since it is going to be doing this function over a series of rows, not just one.

[lucas]

Would be easier to understand what you want if we had an example workbook.

[lanhao]

Unfortunately there's proprietary information in this workbook. I'm sorry on this one I can't give out more information. Hence the reason why I was just wondering how the proper syntax for the formula in vba as a worksheetfunction argument instead fo a cell = "=SUMIF(....)"

(had to edit - was apararently stuck in a loop in my head while typing this)

[lucas]

can you put together a simple demo workbook that shows what your trying to do?

[lanhao]

honestly i would rather just get the syntax on how to enter it in VBA is all, i know how to put a sumif function together in an excel sheet, the problem is the conditional formatting is not alloiwn gme to do what i need it to with the formula instead of answer.

[lucas]

Honestly, I don't think its as simple as revealing the sytax. The following is a sheet change code that uses offset to do a vlookup. You can see that its not simple syntax.....more info is required. Maybe someone else has some insights for you, I hope.
[VBA]
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 3 Then
On Error Resume Next
Target.Offset(, -1) = Sheets("Code Key").Columns(2).Find(What:=Target, MatchCase:=False).Offset(, -1)
End If
If Err > 0 Then MsgBox ("Code not found")
Application.EnableEvents = True
End Sub
[/VBA]

[lucas]

Can you define which cells you wish to perform the sumif on and what the conditions are?

[lanhao]

That is doable, what needs to be done (since the report names will change constantly) is take information from a date range, see how many orders a rep entered, the correct orders, the percentage of them.

What the sum if is used for is simply to calculate out the total number of sales within that date range on a rep by rep basis. It's a a list that is never going to remain static unfortunately. what the two sum if functions are going to do are simply going to output the actual values to the cells where the conditional formatting would apply to, so this way my bosses have an idiotproof way of doing lookups.

Here is how the output ranges are listed:

Col A = Rep Name
Col C = # Correct
Col D = # Entered

I need to have the sumif function set ot use the range information in Col C or C based on the Rep name.

I have been asked to make the things I work on here as idiotproof as possible for them, which I am trying to do for them.

[Norie]

lanhao

The syntax of the SumIf in VBA is basically the same as that in a worksheet.

Say you have this worksheet formula.

=SUMIF(A1:A10, "X", B1:B10)

The VBA equivalent is this.
[vba]
c = Application.WorksheetFunction.SumIf(Range("A1:A10"), "X", Range("B1:B10")
[/vba]

[lanhao]

thanks so very much for the help with the syntax on that - would it be the same if it's for another sheet?