SUMPRODUCT() - Sum values based on conditions

Sometimes we want to sum values from a table based on conditions that we input into another cell. Take a look at the table below. Here we have a table of weekly values for each vendor.

SUMPRODUCT
In this case we want to input on cell B7 the week value and get the total for this week on cell C7. This can be done using the SUMPRODUCT() function. The syntax for this function is like this:

SUMPRODUCT(array1,array2,…) you can have up to 30 arrays.

The formula in C7 for this will:

=SUMPRODUCT((B1:E1=B7)*(B2:E4))

Checks on range B1:E1 for the value in cell B7 and sums the values under that value. On this example the value "Week 2" can be found on C1 so the sum will be done with the values from C2:C4 and the result will be 338.

Another example is putting in B9 the vendor name and get the total for this vendor on cell C9. The formula will be like this:

=SUMPRODUCT((A2:A4=B9)*(B2:E4))

Checks on range A2:A4 for the value in cell B9 and sums the values for that row where it finds the vendor. On this example the vendor "Vendor 3" can be found on row 4 so the sum will be done with the range B4:E4 and the result will be 492.

Finally we want to get the value for Week 2 for Vendor 3. We can use also the SUMPRODUCT function like this:

=SUMPRODUCT((B1:E1=B7)*(A2:A4=B9)*(B2:E4))

It will return the value 114 that corresponds to Vendor 3 (row 4) and Week 2 (column C).

These are simple examples of how you can use the SUMPRODUCT function. It can be used in more complex formulas that I will address later on another article.

5 comentários:

Luis said...

Obrigado, deu jeito!

Everett Henderson said...

Dude, this is really helpful. I'll save so much time using this technique.

Anonymous said...

Hey thank u for your post. I like the SUMIF function and often use it in my job.Watch this video besides and you have the hang on it http://www.excel-aid.com/the-excel-sumif-function.html

Reginaldo said...

Era o que faltava para resolver uma situação complexa.

Nora Pittman said...

Crypto-currency as a modern form of the digital asset has received a worldwide acclaim for easy and faster financial transactions and its awareness among people have allowed them to take more interest in the field thus opening up new and advanced ways of making payments. Crypto.com Referral Code with the growing demand of this global phenomenon more,new traders and business owners are now willing to invest in this currency platform despite its fluctuating prices however it is quite difficult to choose the best one when the market is full. In the list of crypto-currencies bit-coins is one of the oldest and more popular Crypto.com Referral Code for the last few years. It is basically used for trading goods and services and has become the part of the so-called computerized block-chain system allowing anyone to use it thus increasing the craze among the public, Crypto.com Referral Code.

Common people who are willing to purchase BTC can use an online wallet system for buying them safely in exchange of cash or credit cards and in a comfortable way from the thousands of BTC foundations around the world and keep them as assets for the future. Due to its popularity, many corporate investors are now accepting them as cross-border payments and the rise is unstoppable. With the advent of the internet and mobile devices,information gathering has become quite easy as a result the BTC financial transactions are accessible and its price is set in accordance with people’s choice and preferences thus leading to a profitable investment with Crypto.com Referral Code. Recent surveys have also proved that instability is good for BTC exchange as if there is instability and political unrest in the country due to which banks suffer then investing in BTC can surely be a better option. Again bit-coin transaction fees are pretty cheaper and a more convenient technology for making contracts thus attracting the crowd. The BTC can also be converted into different fiat currencies and is used for trading of securities, for land titles, document stamping, public rewards and vice versa.

Another advanced block-chain project is Ethereumor the ETH which has served much more than just a digital form of crypto-currency Crypto.com Referral Code and its popularity in the last few decades have allowed billions of people to hold wallets for them. With the ease of the online world,the ETH have allowed the retailers and business organizations to accept them for trading purposes, therefore, can serve as the future of the financial system.