Sometimes, we need to sum by a category in Google Sheets.
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.