Excel_Kid_663
New Member
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.
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.