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.
For example, we can instead type the following formula into cell I2 to calculate the sum of sales for the “West” stores:
=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:
Excel: How to Use SUMIF Before Date
Excel: How to Use SUMIF Across Multiple Sheets
Excel: Use SUMIFS with Multiple Criteria in Same Column
