Sumif with multiple criteria in same column
This tutorial shows how to use the SUMIF function with multiple criteria in one column, using Excel formulas and VBA
Example: Sumif with multiple criteria in same column
|
This formula uses the Excel SUM and SUMIF functions to sum the values from range (C5:C11) that are associated with values in range (B5:B11) if they meet the criteria that we set, which is equal to "Bread" and "Apples".
|
METHOD 2. Sumif with multiple criteria in same column
EXCEL
|
This formula uses multiple SUMIF functions to sum with multiple criteria. This is appropriate to use if you only have a few criteria to apply, however, if you have a lot of criteria that you want to apply then the formula in Method 1 would be more appropriate.
|
METHOD 1. Sumif with multiple criteria in same column
VBA
Sub Sumif_with_multiple_criteria_in_same_column()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
result = 0
For x = 5 To 11
If ws.Range("B" & x) = "Bread" Or ws.Range("B" & x) = "Apples" Then
result = result + ws.Range("C" & x)
End If
Next x
ws.Range("F4") = result
End Sub
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Column where value to sum: Select the column that captures the values that you want to sum if a set criteria is met by changing column reference "C", in ws.Range("C" & x), in the VBA code.
Data column: Select the column that captures the values that you want to apply the criteria against by changing column reference "B", in ws.Range("B" & x), in the VBA code.
Row Range of data and values: In this example the macro loops through a range of cells to test is a criteria is met and then sums the associated value. To select the rows through which to loop through you can change the values that are assigned to "x" (rows 5 to 11).
Worksheet Selection: Select the worksheet which captures the data to be tested against the criteria and the associated values to be summed by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code.
Column where value to sum: Select the column that captures the values that you want to sum if a set criteria is met by changing column reference "C", in ws.Range("C" & x), in the VBA code.
Data column: Select the column that captures the values that you want to apply the criteria against by changing column reference "B", in ws.Range("B" & x), in the VBA code.
Row Range of data and values: In this example the macro loops through a range of cells to test is a criteria is met and then sums the associated value. To select the rows through which to loop through you can change the values that are assigned to "x" (rows 5 to 11).
Worksheet Selection: Select the worksheet which captures the data to be tested against the criteria and the associated values to be summed by changing the Analysis worksheet name in the VBA code. You can also change the name of this object variable, by changing the name 'ws' in the VBA code.
Explanation about how to use the sumif function with multiple criteria in same column
EXPLANATION
EXPLANATION
This tutorial shows how to use the SUMIF function with multiple criteria in one column, using Excel formulas and VBA.
This tutorial provides two Excel methods that can be applied to sumif with multiple criteria from the same column.
The first method uses a combination Excel SUM and SUMIF functions, where the multiple criteria is entered between the { }. You can add whatever other criteria you want to sum for.
The second method simply uses multiple SUMIF functions with the a single criteria applied to each SUMIF function and sums each SUMIF function. This would only be suitable to use if there are only a few criteria that you want to apply, however, if you have a large number of criteria that you want to apply then you should use the formula that is applied in Method 1.
The VBA code in this tutorial loops through each data cell to identify if the specific criteria is met and if it is it will include the associated value to be summed.
FORMULA (using SUM and SUMIF functions)
=SUM(SUMIF(data_range,{"criteria1","criteria2",...},sum_range))
=SUM(SUMIF(data_range,{"criteria1","criteria2",...},sum_range))
FORMULA (using SUMIF function)
=SUMIF(data_range,"Bread",sum_range)+SUMIF(data_range,"Apples",sum_range)+...
=SUMIF(data_range,"Bread",sum_range)+SUMIF(data_range,"Apples",sum_range)+...
ARGUMENTS
data_range: The range of cells you want to test the criteria against.
sum_range: The range of cells you want to sum from.
criteria1: The first criteria that is used to determine which of the cells, from the specified range, should be summed.
criteria2: The second criteria that is used to determine which of the cells, from the specified range, should be summed.
data_range: The range of cells you want to test the criteria against.
sum_range: The range of cells you want to sum from.
criteria1: The first criteria that is used to determine which of the cells, from the specified range, should be summed.
criteria2: The second criteria that is used to determine which of the cells, from the specified range, should be summed.
RELATED TOPICS
| Related Topic | Description | Related Topic and Description |
|---|---|---|
| Sum values if greater than | How to sum values that are greater than a specific value using Excel and VBA | |
| Sum values if less than | How to sum values that are less than a specific value using Excel and VBA | |
| Sum values if cells are blank | How to sum value if cells are blank using Excel and VBA | |
| Sum values if cells contain text | How to sum value if corresponding cells contain text using Excel and VBA |
RELATED FUNCTIONS
| Related Functions | Description | Related Functions and Description |
|---|---|---|
| SUMIF Function | The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria | |
| SUM Function | The Excel SUM function returns the sum of all numbers in a specified range |