SumIf in VBA (Conditional on variable)

Related searches

I am trying to write two VBA macros:

The first takes a Variable (VariableA) and checks the value of another Variable (VariableB). Depending on the value of VariableB, a multiplier is applied to VariableA. Then the final value of VariableA (with the multiplier) is taken and output into a new variable (VariableC). I have tried an "If Then Else" statement and that doesn't seem to work (most likely because I am doing it incorrectly).

The second macro looks to sum that will take VariableC and sum the variable depending upon the value of VariableB and produce a single output for each occurrence of VariableB.


VariableA VariableB Multiplier VariableC 100 1998 1.4 140 100 1998 1.4 140 100 1999 1.7 170 100 1999 1.7 170 100 2000 2.0 200 100 2000 2.0 200

I have tried to to use SumIfs within VBA but I keep getting an error 13, which I think is coming from the format difference between VariableB and VariableA. Even when I set the two variables to the same format (Integer) I still get the error. When I debug it, it points me towards VariableB as being the issue.

Ideally I am trying to get my final output to look something like this:

VariableB SumofVariableC 1998 280 1999 340 2000 400

I am new to VBA and would appreciate any assistance that anyone could offer. Thank you.

As far as the variable multiplier is concerned try using a Select Case

Dim Vari as Integer
Dim Multi as Single
Vari = VariableB 

Select Case Vari
    Case Is = 1998
        Multi = 1.4
    Case Is = 1999
        Multi = 1.7
    Case Is = 2000
        Multi = 2.0
    Case Else
        Multi = 1.5
End Select

As far as the second issue, I'm not sure I understand whats going on, but it seems that the variables are mismatched. You may need to convert/cast them. An Integer data type is not the same as a Single data type (for decimals). Use the convert to integer Cint to change the product into an integer before getting to your error point.

SUMIF Function With Multiple Criteria using VBA in Microsoft Excel, In this article, we will learn how to use SUMIF function in VBA with multiple criteria in Excel using VBA code. To get the output, we will use a combination. I'm new to VBA and trying to figure this one out, I did not work with functions yet. I'm trying to automate the SUMIF formula on my VBA sheet. The Rec sheet will contain a dynamic SUMIF formulas based on a variable/range in Column D based on Column B values. the SUM range will be coming from the CB sheet, matching column K and summing column L.

Pivot table may works on your problem and can give you accurate result with easy navigation on its raw and column

How to use sumif function in VBA, How to use the SUMIF function in VBA. The SUMIF function is useful in inventory management Duration: 13:14 Posted: Nov 26, 2015 The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria. Learn how to apply the Excel SUMIF function using Excel and VBA.

WorksheetFunction.SumIfs method (Excel), SumIfs (Arg1, Arg2, Arg3, Arg4, Arg5, Arg6, Arg7, Arg8, Arg9, Arg10, Arg11, Arg12, expression A variable that represents a WorksheetFunction object. Have questions or feedback about Office VBA or this documentation? The Microsoft Excel SUMIF function adds all numbers in a range of cells, based on a given criteria. If we want to use or automize some operations with VBA in Excel there are three ways we can be involved with SUMIF () when we are using VBA: The Range.Formula (), Range.FormulaR1C1 (), Range.FormulaArray () properties.

SUMIF From VBA, If we want to conditionally sum using the SUMIF() worksheet function in an error returned by SUMIF() will be held within the vResult variable� Pressing ALT + F8 shortcut key for opening Macro window & then select the macro. Alternatively, you can press F5 to run the code in VBA screen. After executing the macro we will get the output in cell H8 This is how we can use SUMIF multiple criteria in VBA to calculate the total sales between 2 dates.

SUMIF in VBA, i want a variable to have the value of a sumif condition. That is, say. SUMACT = SUMIF(E28:I28,"",E27:I27) Thanks, Bhanu� The VBA code was to be run on a single cell’s formula and instantly navigate the user to the source data and filter using just the criteria within the SUMIFS function. This would show the user the detail (other fields in the source data’s table) associated with the results of the SUMIFS function being used within the cell.

The SUMIFS (that’s ifs, plural) function works like SUMIF function except that it enables you to specify more than one criteria range that controls when a certain range of values are summed. Its syntax is a little bit different: SUMIFS ( sum_range,criteria_range,criteria,…) For this function, the sum_range argument specifies all the possible values that can be summed, the criteria_range specifies the cells with all the entries that are to be evaluated by the if criteria, and the criteria

  • Use a pivot table.
  • Thank you for the advice. I think a pivot table might provide me part of the solution that I need.
  • Thank you for the advice. I think a pivot table might provide me part of the solution that I need.
  • welcome :) if this solution helps you, dont forget to vote this up and mark this as an answer ")