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.

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

Example:

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.

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

=SUMIFS(

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

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.

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.

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:

“Noida”

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.

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.

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

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

=SUMIFS(E3:E12,D3:D12,D3,E3:E12,”<50000”)

Now, press “enter”.

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.

It’s easy, isn’t it?