How to Filter Rows by Aggregated Values

Wondering how to filter your rows based on aggregated values?

Sure, you can group your data inside Microsoft Excel and Google Sheets using Pivot mode. And depending on your requirements, you can also summarize it using various aggregation functions like sum, average, max, min, and more.

But once you do that, what if you want to further filter your data by aggregated values?

What if you want to find all the product categories having sales above a certain threshold? Or what if you want to filter out the product categories with less than 10 products?

Even though Microsoft Excel and Google Sheets are spreadsheet giants and the best of the best at basic tasks, performing aggregated filters on these platforms is either complex or impossible.

Luckily, Gigasheet makes performing such complex operations possible and a piece of cake.

In this blog post, I’ll show you how you can filter your data by aggregated values inside Gigasheet. Let’s dive into it!

How to filter your data by aggregated values inside Gigasheet

If you have read my blog posts before, you know how much I love eCommerce. Don’t worry if you haven’t – in this blog post, I’ll show my love for eCommerce as I play around with a Shopify store’s inventory management dataset. This dataset comprises the following columns:

1. Product Name
2. Brand
3. Price
4. Currency
5. Availability
6. Stock
7. #Sold in October 2022
8. #Sold in November 2022
9. #Sold in December 2022
10. Primary Category
11. Sub-Category

Here’s a screenshot of the dataset for better understanding:

To find aggregated values for each of the columns, I can click at the very bottom and choose the aggregation function I’d like. For example, if I want to find the total number of products sold in October, I can just click on the blank drop-down at the bottom and select “Sum.”

Similarly, I can perform different types of aggregation operations for all columns inside my dataset. But honestly, we don’t need a filter here.

We’ll need a filter when we group our data.

So, for the purpose of demonstration, let’s come up with a hypothetical scenario.

Let’s say I want to get a list of product categories with more than $1,000 in product sales in October 2022. Now that’s a complicated situation right there. If it were a product with more than $1,000 in sales in October 2022, I could have easily applied a normal filter and got my answers. Doing this in Excel or Google Sheets would also have been a piece of cake.

But in this scenario, we need to identify product categories with more than $1,000 product sales in October 2022.

So, the first thing I’ll have to do is – group my data by column “Product Category.”

Great news – I don’t have to use the Pivot table to do so. While Gigasheet does have Pivot functionality that you can easily tap into, you can just click on “Group” and select the column you want to group your data by – in our case, it’s “Product Category.”

Here are the results:

There are a total of 19 product categories in our dataset. Under “#Sold in October 2022,” I clicked on “Sum” to calculate sales in October 2022 for all the categories.

As soon as I clicked on “Sum,” Gigasheet took no more than two seconds to calculate the sum for the entire column group “#Sold in October 2022” for all the categories.

Now, as per my condition, I want to find out product categories with more than $1,000 sales in October 2022.

So, I’ll just apply a filter.

Upon clicking on “Filter,” you’ll find that there are two options to choose from:

• Rows – You can filter by the original rows in your dataset.
• Groups – You can filter your data based on the grouping you’ve applied.

In our case, we have to filter based on groups – as we already have applied a grouping, and we want to filter based on the aggregated SUM function for the column group “#Sold in October 2022.”

After selecting the “Groups” option at the top, we’ll set the following filter:

Here are the results:

Product categories with less than or equal to $1,000 sales in October 2022 have been removed.

But let’s not end the demonstration here. Let me unlock the full power of this functionality in front of you.

Let’s say I want to identify the product categories with greater than $1,000 sales in October 2022 and the average product price greater than $25.

In this case also, we’ll group our data by “Product Category” – after which we’ll apply the following filter:

Here are the results:

From here, you can click on the drop-down menu to access data after applying the filters.

But now imagine if you had to do this on Microsoft Excel or Google Sheets. Look, we’re not saying, these platforms can’t perform complex operations, or our platform is 1000x better than these spreadsheet platforms.

These spreadsheet platforms are great in various areas – but have some limitations. And the worst part is – these platforms have a steep learning curve.

Whereas Gigasheet makes handling and playing around with spreadsheets easier. Add the ability to work with big data spreadsheets – spreadsheets that are humongous in size and loaded with millions of rows.  

Don’t believe us? Try out Gigasheet today.

It’s FREE to use FOREVER.