Can someone help me understand this formula please?

[Excel_Kid_663]

Hi there I was wondering if someone could help me understand this formula. I understand what it does and I understand what results it returns, I just dont understand HOW. Any help would be very much appreciated, ill explain what I know underneth:


So as an overview what this formula does is filters data in a table by two criterias, however it also needs to be able to show all the criterias too and uses the key work "All"

The formula does work as it has been in use for sometime now. I have changed some of the cell references to make it easier to understand.


=SUMPRODUCT(--(Table1[Month]="January")

,--(Table1[Appointment]="Attended")

,--((Dashboard!$X$37="All")+(Dashboard!$X$37=Table1[Territory])>0)

,--((Dashboard!$U$37="All")+(Dashboard!$U$37=Table1[Campaign])>0))


So the sum product part is easy for me to understand: Its Looking in table 1, column named month and finds where the month is = to January then looks in table 1 Column appointment and finds where the entries are = to attended then I get stuck. I dont understand what this does. I understand that the + is an OR meaning so maybe its saying return everyhting if = to all OR return where the value of either criteria is TRUE to whatever exists in the initial cell reference? (Hence the ,--)


Does that make sense?


Many thanks in advance for helping me understand this.

 

[amrit1186]

Hi Excel_Kid_663


Your understanding for the use of '+' sign is absolutely correct. It is being used to evaluate OR condition.


The -- is being used to convert the array returned as True/False to 1/0.


Hope the explaination helps.


Amritansh

 

[Excel_Kid_663]

But which part is saying if this is = to "All" then give me everything in that range? rather than say territory 1 or territory 2 for example?

 

[Luke M]

Let me take a crack at it. Let's say that we have an array like so

[pre]

Ford
Jeep
Jeep
Ford
Dodge
And we have two arrays in our formula, written like this:

[code](MyRange = "Ford") + (MyRange = "Dodge")

Now, an important thing to note is that both array are looking at the saem range, and the two criteria are mutually exclusively. By that I mean that the values can either equal the first criteria, or the latter, but not both. This is important as seen in next step. So, the first criteria array will produce this:

[pre][code]True
False
False
True
False

and the second will produce this

False
False
False
False
True[/code][/pre]
When we add them together, the True/False gets converted to 1/0, and we get a new array, like so:

1
0
0
1
1[/code][/pre]
See how this new array is a combination of both? This is exactly how the OR logic works. The reason we needed criteria to be mutually exclusive is so that we don't get any 1+1=2 scenarios. So, in your formula, the SUMPRODUCT is able to handle whether you have a specific criteria, or the All criteria.

 

[Excel_Kid_663]

I get that Luke, thanks for explaining. I understand that the OR formula is saying give me a count of this criteria OR this criteria but the formula I posted is very much different to this for the following reason:


Lets say the otions in the cell reference in the first criteria are: ALL, Territory 1, Territory 2 and Territory 3.

And the options for the cell reference in the second criteria are: All, Campaign 1, Campaign 2


If I choose Territory 1 campaign 1 then the formula does:


Return me all counts of all occurances where the month is = to "January", appointment is = to "Attended", Territory is = to "Territory 1" and Campaign is equal to "Campaign 1"


This is actually a very simple SUMPRODUCT (Or beter with countifs in 2010)


The problem comes when we change the criteria range to "All", because "All" does not exist as a value in the table. It's not looking for all, infact its not looking for anything, So what I am failing to see is how "All" returns all results because it doesnt seem to reference anything other than >0 ( which i believe is Greater than false )


Sorry if i'm being simple here, I dont like using things I do not understand it makes me feel like im cheating.

 

[Luke M]

Ah, I see what you mean. More nuances to the formula then I originally thought. Okay, starting with our interesting piece:

,--((Dashboard!$X$37="All")+(Dashboard!$X$37=Table1[Territory])>0)


I see now that there are 2 criteria arrays being added together, and then the subtle twist is that they are part of another criteria check, the ">0" bit. So what's going on here?

The first criteria is interesting in that it only produces a single result, a True/False. The second criteria however will produce an array. So what happens when we add those two together? Let's get an example. Here's our array:

[pre]

Canada
Canada
US
Europe
And let's say X37 = "Canada". Our two arrays then will look like

{False}

and

{True,True,False,False}


If we add them together, the first array evaluates to 0, and it's value gets added to each of components in 2nd array. This produces the numerical array:

{1,1,0,0}


Hoever, if X37 = "All", our arrays would be:

{True}

and

{False,False,False,False}

and adding them together, again having the first array get distributed, causes array to become:

{1,1,1,1}

As you can see, this would include all results, which is what we would expect from the "All" selection.

For this situation, I don't think the ">0" bit is actually needed. However, if there was a chance for criteria to be added and our last array became something like:

{1,2,1,1}

The >0 bit would convert this back down to 1's and 0's, which is what's needed for SUMPRODUCT to work correctly.


Still confused? Let's try using formula evaluate. Paste this into cells A1:B5

List	Criteria
good	ALL
good
bad
bad

[/pre]
Then, formula in C2:

=SUMPRODUCT(--((B2="ALL")+(A2:A5=B2)>0))

Use the evaluate formula (under Formula ribbon) to step through how things are working. Play around a bit, changing the criteria to either good/bad/All and see how the formula handles things.

 

[Excel_Kid_663]

I see I think I get it now, if the first criteria is "ALL" then its true, and the first true false value will affect the second set so in this case all become true. But that is also true for any blank cells also, so this would work only for a table range or a static dataset. The formula evaluator helped, however i didnt know that the first set value converts all false values after it if true, the evaluator shows it but doesnt explain why. I also looked into the >0 bit, Not sure I fuly understood that bit however it only seemed to be a problem where the word 'All' actually existed in the dataset it retured a value of 2 (Which i found weird as i thought the -- made it to either 0 or 1, i dont understand how im getting 2) But anyway thank you very much sir, I have one final question you may be able to help with. If i wanted to do the same but instead of giving me a count of instances I wanted a SUM of a certain column in those instances only, what formula would i need to use?


Regards

 

[Luke M]

only seemed to be a problem where the word 'All' actually existed in the dataset it retured a value of 2 (Which i found weird as i thought the -- made it to either 0 or 1,

If both arrays have a True in the same spot, this is that same as having a 1 in both slots, so adding them together creates a 2. This causes a problem when SUMPRODUCT multiplies the arrays, hence the >0 to convert is back down to just 0 and 1. The -- is just there to convert the text strings of True/False into numerical values.


If you want a sum, just add it as another array, like so:

=SUMPRODUCT(--(Table1[Month]="January")

,--(Table1[Appointment]="Attended")

,--((Dashboard!$X$37="All")+(Dashboard!$X$37=Table1[Territory])>0)

,--((Dashboard!$U$37="All")+(Dashboard!$U$37=Table1[Campaign])>0),

(NewArrayToAddValues))