Table of Contents
The SUMIF function in Excel allows users to add up values in a range based on a specified criteria. To use this function with a horizontal range, the user must first specify the criteria and the range of cells to be evaluated. The function will then sum up any values that meet the given criteria within the specified range. This can be useful for analyzing data in a more organized and efficient manner. By utilizing the SUMIF function with a horizontal range, users can easily calculate and track specific data points in their spreadsheet.
Excel: Use SUMIF with Horizontal Range
Often you may want to use the SUMIF function in Excel to sum values based on criteria in a horizontal range.
For example, suppose you have the following dataset that shows sales made at various retail stores during various transactions:
The following example shows how to use a SUMIF function to sum the values in each row if they are equal to a specific retail store in the horizontal range of the first row.
Example: How to Use SUMIF with Horizontal Range in Excel
Suppose we have the following dataset that shows sales made at various retail stores during various transactions:
Suppose we would like to calculate the sum of values in each row where the corresponding store name in the first row is “East.”
We can type the following formula into cell I2 to do so:
=SUMIF($B$1:$G$1, "East", B2:G2)
We can then click and drag this formula down to each remaining cell in column I:
Column I now shows the sum of sales in each row where the corresponding store name in the first row is “East.”
For example:
- The sum of East sales for transaction 1001 is: 7 + 5 = 12
- The sum of East sales for transaction 1002 is: 6 + 3 = 9
- The sum of East sales for transaction 1003 is: 6 + 3 = 9
And so on.
If you’d like to calculate the sum of sales for a different store, simply use a different store name in the SUMIF function.
=SUMIF($B$1:$G$1, "West", B2:G2)
We can then click and drag this formula down to each remaining cell in column I:
Column I now shows the sum of sales in each row where the corresponding store name in the first row is “West.”
Additional Resources
The following tutorials explain how to perform other common operations in Excel: