How to sum by category in Google Sheets

Sometimes, we need to sum by a category in Google Sheets. For example, you might need to sum up points scored in all games per team in a tournament season.

We can use SUMIF function, SUMIFS function and UNIQUE function for this. 

2 Methods to sum by category in Google Sheets 

  • Summing according to a given range using SUMIF
  • Summing according to a given range using UNIQUE and SUMIF

Method 1: Summing according to a given range using SUMIF

For instance, let’s take the following dataset. We wish to calculate units per item sold. Now, the items are of four categories in total. We have a pencil, binder, desk, desk-set. 

Step 1: Applying the SUMIF function. 

The syntax of SUMIF function is = SUMIF(range, criterion, [ sum_range])

Here, range represents the range of cells containing all the categories, criterion represents the category to be summed, sum range represents the values to be summed by the particular category..

For this instance, to find out the total units of pencils, we use  SUMIF( D2:D44, “PENCIL”, E2:E44).

Similarly, to find the total units of another category, we simply replace pencil with that category. To get the total units of pens, we use SUMIF( D2:D44, “PEN SET”, E2:E44).


Method 2: Summing according to a given range using UNIQUE and SUMIF functions

Step 1: Finding the categories using the UNIQUE function.

Let’s take the same  example. 

The UNIQUE function’s syntax is =UNIQUE( range of data, [by column], [ exactly_once]}

Here we need to know the different categories of items in the dataset.So, syntax is =UNIQUE( D2:D44).

The column gets populated with the list of unique categories. In this case, there are five options.  

Step 2: Totalling value per category using SUMIF.

The syntax of SUMIF function is = SUMIF(range, criterion, [ sum_range]).

For pencil, we use = SUMIF(D2:D44, I2, E2:E44).

You will get a Suggested autofill prompt. Click the tick icon to accept the autofill.

We get the result as such. 

Conclusion

We hope these methods have helped you. Let us know in the comments below. 

Leave a Comment