Using SUMIFS

Question

I have a large excel table with 52 rows of items and the cost of each item. I want to reaggregate the items within 9 major categories

Answer 1

Use SUMIFS (or SUMIF) with a category column that maps each item to one of the 9 major categories, then sum by that category.

1. Ensure the table has at least these columns, for example:
   • Column A: Item
   • Column B: Cost
   • Column C: MajorCategory (values like Cat1, Cat2, …, Cat9)
2. Create a summary area listing the 9 major categories, for example in E2:E10.
3. In F2, enter a SUMIFS formula to sum costs for the category in E2:

      =SUMIFS($B$2:$B$53, $C$2:$C$53, E2)
   

   • $B$2:$B$53 is the range of costs to sum.
   • $C$2:$C$53 is the range that contains the major category for each item.
   • E2 is the category name to aggregate.
4. Copy the formula in F2 down to F10 to get totals for all 9 categories.

If there is only one condition (just the category), SUMIF can also be used:

=SUMIF($C$2:$C$53, E2, $B$2:$B$53)

For multiple conditions (for example, sum by category and by region or date range), extend SUMIFS with more criteria ranges and criteria, such as:

=SUMIFS($B$2:$B$53, $C$2:$C$53, E2, $D$2:$D$53, "South")

---

References:

• Sum values based on multiple conditions
• SUMIFS function
• SUMIF function

Answer 2

Hi,

Assuming the items are in range A2:A53 and cost in range B2:B53, enter this formula in cell D2

=Groupby(A2:A53,B2:B53,SUM)

Hope this helps.