Lesson#132: How to make the function SUMPRODUCTIF

In this post I will show you the trick to make the function SUMPRODUCTIF. We have SUMPRODUCT fuction in excel. But how to make a SUMPRODUCT fuction that follows criteria like SUMIF.

Here I have made an example.

Here’s how to make the SUMPRODUCT.

Here’s the total value of all items can be found by SUMPRODUCT. But if I want to find the total value category wise then I need to modify the formula.

Here it is making SUMPRODUCT for the FRUIT category only.

And here it is making SUMPRODUCT for the VEGETABLE category only.

Instead of using this formula =SUMPRODUCT($F$2:$F$10,G$2:G$10) I have used formula =SUMPRODUCT(–($E$2:$E$10=$E12),$F$2:$F$10,G$2:G$10)

What is the Double negative (“–“) does?

Double unary converts the conditions in text value to act like a numeric one and forces the rest of the function work properly considering the conditions.

We will discuss more about double unary in next posts.

Download the file from here.

Puspendu is the founder author of Excelabcd. He is a creative person, blogger and Excel-maniac guy.

Tagged with: ,

Leave a Reply

Your email address will not be published. Required fields are marked *