There are situations when what we have is thousands of data but what we need is a bit of it. Not just that, it’s obvious that the data we need includes a lot of specifications like sales below 20000, or areas of North region only, etc. 

Previously: How to round a number down with ROUNDDOWN Formula?

So, to get your data as per your expectations, this is what we do:


SUMIFS formula

As you can see in the image above, we have product ID, its code, the place it is in and the sales it has generated.

SUMIFS formula

Now to get started with what we actually want, this is what we will do:

We will use the function of SUMIFS, the formula for the same is


The following formula requires sum range, criteria range 1, criteria 1, and so on…

SUMIFS formula

Now the first thing that we will do is select the sum range, i.e. the main data. So, in this case our main data is the sales made.

SUMIFS formula

After selecting the same, we come to the point of criteria range 1.

Now to understand for what is criteria range 1, it is basically the specifications that we talked above.

So, let’s say we want the sales data of Noida, so our criteria range 1 will be all the places mentioned.

SUMIFS formula

After criteria range 1, its an obvious that we will come to the point of what sort of criteria do we want to apply! So, as we want the products of Noida, so we will either select Noida, or we will write the same as:


Kindly note that whenever you write something in a formula that too in the quotation marks, you need to write the word/s exactly the way it is in your data. For example, we cannot write Noida, we have to write Noida or else the formula will not catch up.

SUMIFS formula

After jotting down the criteria 1, the aspect of criteria range 2 begins. You can put as many criteria as you want, it all depends on the type of data you are looking for. 

Now, in the above example, we have taken our criteria range 2 products sales only. 

SUMIFS formula

And our criteria 2 is “<50000>” i.e. we want all those product’s sales which are below 50000 and are from Noida!

SUMIFS formula

This makes our overall formula as per the example that we have taken as follows:


Now, press “enter”.

SUMIFS formula

The total sales of all those products which were placed in the city of Noida and which had the sales below 50000 comes out to be 123000.

SUMIFS formula

It’s easy, isn’t it?