Table of Contents
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.”