Sum values associated with most frequently occurring text
To sum values associated with most frequently occurring text you can apply an Excel or a VBA method using a combination of Excel SUMIF, INDEX, MODE and MATCH functions
Example: Sum values associated with most frequently occurring text
|
The formula uses a combination of the Excel SUMIF, INDEX, MODE and MATCH functions to sum the numbers associated wit the most frequently occurring text.
Given the Excel MATCH function returns the position of the first match, by applying the same range to the lookup value and the lookup array the MATCH function will return a range of results (numbers) where each number represents the first occurring position of the value. The formula then applies the Excel MODE function to return the most frequently occurring number from the range of numbers calculated through the use of the Excel MATCH function. In this example the most occurring number will be (1) associated with Bread. The Excel INDEX function is used to identify the most frequently occurring number. Using the same range as was applied in the Excel MODE and MATCH functions (B5:B11) it would return the first value in the range, which in this example is Bread. The final step is to sum the numbers associated with the most frequently occurring text by using the Excel SUMIF function. Please note that this formula will return an #NA error if at least one of the cells in the selected range is blank. |
Sub Sum_values_associated_with_most_frequently_occurring_text()
'declare a variable
Dim ws As Worksheet
Dim ws As Worksheet
Set ws = Worksheets("Analysis")
'apply the formula to sum values that are associated with most frequently occurring text
ws.Range("F4").Formula = "=SUMIF(B5:B11,INDEX(B5:B11,MODE(MATCH(B5:B11,B5:B11,0))),C5:C11)"
ws.Range("F4").Formula = "=SUMIF(B5:B11,INDEX(B5:B11,MODE(MATCH(B5:B11,B5:B11,0))),C5:C11)"
End Sub
PREREQUISITES
Worksheet Name: Have a worksheet named Analyst.
Text Range: Ensure that the range that contains the text is captured in range ("B5:B11") in the Analysis worksheet.
Sum Range: Ensure that the sum range (corresponding range to the text range) is captured in range ("C5:C11") in the Analysis worksheet.
Worksheet Name: Have a worksheet named Analyst.
Text Range: Ensure that the range that contains the text is captured in range ("B5:B11") in the Analysis worksheet.
Sum Range: Ensure that the sum range (corresponding range to the text range) is captured in range ("C5:C11") in the Analysis worksheet.
ADJUSTABLE PARAMETERS
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Text Range: Select the range that captures the text by changing the range ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the sum range (corresponding range to the text range) by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Output Range: Select the output range by changing the cell reference ("F4") in the VBA code to any cell in the worksheet, that doesn't conflict with the formula.
Text Range: Select the range that captures the text by changing the range ("B5:B11") to any range in the worksheet, that doesn't conflict with the formula.
Sum Range: Select the sum range (corresponding range to the text range) by changing the range ("C5:C11") to any range in the worksheet, that doesn't conflict with the formula.
Explanation about the formula used to sum values associated with most frequently occurring text
EXPLANATION
EXPLANATION
To sum values associated with most frequently occurring text you can apply an Excel or a VBA method. The formula used to sum values associated with most frequently occurring text is driven by a combination of Excel SUMIF, INDEX, MODE and MATCH functions.
To sum values associated with most frequently occurring text you can apply an Excel or a VBA method. The formula used to sum values associated with most frequently occurring text is driven by a combination of Excel SUMIF, INDEX, MODE and MATCH functions.
FORMULA
=SUMIF(text_range,INDEX(text_range,MODE(MATCH(text_range,text_range,0))),sum_range)
=SUMIF(text_range,INDEX(text_range,MODE(MATCH(text_range,text_range,0))),sum_range)
ARGUMENTS
text_range: The range of text from which you want to identify the most frequently occurring text and sum the numbers associated with that text.
sum_range: The range of cells you want to sum from that are associated with the text_range.
text_range: The range of text from which you want to identify the most frequently occurring text and sum the numbers associated with that text.
sum_range: The range of cells you want to sum from that are associated with the text_range.
RELATED TOPICS
| Related Topics | Description | Related Topics and Description |
|---|---|---|
| Return most frequently occurring text | To return the most frequently occurring text you can use a combination of the Excel INDEX, MODE, MATCH and IF functions that can be applied into Excel or VBA | |
| Count most frequently occurring text | To count the most frequently occurring text you can use a combination of the Excel COUNTIF, INDEX, MODE, MATCH and IF functions that can be applied into Excel or VBA |
RELATED FUNCTIONS
| Related Function | Description | Related Functon and Description |
|---|---|---|
| SUMIF Function | The Excel SUMIF function returns the sum of all numbers in a specified range based on a single criteria | |
| INDEX Function | The Excel INDEX function returns a value that is referenced from a specified range | |
| MATCH Function | The Excel MATCH function searches for a specified value in a range and returns its position relative to the range |