=SUMPRODUCT(N(A1:A10='Luke Skywalker'),N(B1:B10='West'),D1:D10)Д«ut when it comes down to it, the people who are really smart at Excel figured out that Excel is fastest when it uses the unary operator (the '-' sign) twice.ДЇeel free, if you have trouble, to use the *1 or +0 or N() if you'd prefer. So we need to turn them into numbers first. When it does the math for this, it will multiply all the zeroes and 1's and values together, and will always come out with a number. So if you use math in sumproduct, you don't need the double-minus: Well, Excel forces things to be numbers when you do math on them. Type =A1-1 into another cell, and you will get -1Д«ut wait, if it's not a number, why can it do math?! Type =A1=0 into another cell, and you will get FALSE as the result. It needs to turn them into numbers first. This post is part of our spreadcheats seriesД®xcel has trouble doing math with text values. I suggest reading the excel array formula examples, sumif with multiple conditions and other excel formula tutorials. Go ahead and explore the possibilities, then come back and share your tips with us. This post is meant to open the door for you. SUMPRODUCT formula can do much more once you understand how it works. Share your SUMPRODUCT formula Tips & Tricks Why? That is your home work to figure out. When you put everything together and multiply, it just works.The portion D1:D10 is just returning all the sales figures.The portion -(B1:B10='West')is doing the same, but gets 1s when the value is вЂњWestвЂќ.The portion -(A1:A10='Luke Skywalker')is looking for Luke Skywalker across planetary systems in all universes рџ‰ It is going to give us a bunch of ONEs and ZEROs, one if the cell has Luke, Zero if the cell has something else.Okay, lets take a minute and try to understand WTF (what the formula) is doing. Using SUMPRODUCT as an array formulaДЄssuming, the data is in range A1:D10, with Name in column A, Region in B, Product in C and Sales in D, the SUMPRODUCT formula is, That is right, my friend, we can use SUMPRODUCT to do just this (and much more). Use a pivot table ДЄctually, there is a hidden third option, use SUMPRODUCT.

This is simple, you will write a SUMIF formula and use the Name column as вЂњcriteria rangeвЂќ and Sales column as вЂњsum rangeвЂќ.Д«ut, wait a second, you want to find how many units sales person вЂњLukeвЂќ sold in the region вЂњwestвЂќ. Now, you want to know how many units the sales person named вЂњLukeвЂќ sold. Lets say you have a list of sales data with columns Name, Region, Product and Sales.

But all that will change in the next 2 minutes, keep reading. So, for ex: if you have data like in another list, and if you apply SUMPRODUCT, you will get 120 (because 2*5 + 3*10 + 4*20 is 120).ДЄt this point it might seem like an almost useless function. The syntax is =SUMPRODUCT (list 1, list 2. It takes 1 or more arrays of numbers and gets the sum of products of corresponding numbers. The sum-product formula syntax is very simple. But once you understand how excel works with lists (or arrays) of data, the SUMPRODUCTвЂ™s relevance becomes crystal clear. Today we will learn a new and exciting excel formula вЂ“ the all powerful SUMPRODUCT.ДЄt the outset SUMPRODUCT formula may not seem like all that useful.

Sumproduct excel