Art I want to quickly cover a formula here called some product and what the product formula does is
multiply corresponding cells from multiple arrays and return the sum of the products.
That probably sounds like complete gibberish.
It's a little complicated to describe in words but realistically it's not that bad once you understand
what it's doing.
So first the syntax is actually quite simple you just list out the arrays that you want to take the
sum product.
Typically these are entire columns.
Note that all your arrays have to have the same dimensions here.
But to look at an example let's say I've got a very simple data set here just three products along with
quantities and prices.
If I to take the total revenue for all my data all my products I could go row by row and say calculate
the revenue for apples two times 50 cents.
Revenue for bananas four times the dollar oranges three times eighty cents.
And then add those product level revenues to get my total of seven dollars and 40 cents some product
does the same thing just in one step.
So if I take the sum product of column B quantity and Column C price it's doing that exact same process
calculating products row by row and then summing the results to get the same value.
So some product is often used with filters.
So that was a simple example but sometimes you might want to say take the some product but only in cases
where this column takes on a certain value to do that you can just replace your array reference with
the array reference followed by an equal sign followed by what you want to filter on.
So in this case I want a filter where column A.
Only equals Shahs.
And you can write it just like the normal formula.
The only difference in this is a weird little nuance with Excel is that if you don't have a filter you
separate your raise with commas.
If you do have a filter you separate your raise with multiplication signs and you can add as many filters
as you want.
Here in this last example filtering or taking some product only where column equals shaughs and column
B equals Apple.
And then when those two are true it's also multiplying columns C and D.
So one kind of peek under the hood to understand how it's really working is to compare how you see the
data versus how Excel is interpreting the data.
So any time you've got a filter here it's essentially a logical test that Excel evaluates behind the
scenes.
So when I say 8 to raise 17 equal shaughs that gets translated on the back and into a series of zeros
and ones where it's zero.
If my test is false.
So if the store does not equal shots and a 1 if my test is true.
Same deal with Column B.
So 5 B equals Apple.
It's going to look like a 1.
Every time the product is out Bill and 0 otherwise and again you don't actually see these ones and zeros
.
This is just kind of how Excel is doing the math behind the scenes.
So if we were to have some product formula like this which is taking the product of a times b times
c times d as you can see only rows that satisfy all conditions or all filters will produce a non-zero
sum.
Because as soon as one of my filters fails I'm going to get a zero in that row which is going to make
the entire row zero out or be excluded from the sum.
So basically that's how the filter working behind the scenes.
And that's some product.
In a nutshell