• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct and curly brackets question

mugshaw

New Member
Why doesn't this formula work:

=SUMPRODUCT(SIGN((njdata[StoreType]={"SIS","SIS M","SIS L","SIS P"})*(njdata[Season]={"AW16","SS16","AW15"}))*njdata[revenue])

When this one does:

=SUMPRODUCT(SIGN((njdata[StoreType]={"SIS","SIS M","SIS L","SIS P"})*((njdata[Season]="AW16")+(njdata[Season]="SS16")+(njdata[Season]="AW15")))*njdata[revenue])

The difference is that the first formula has two curly brackets defining the criteria to return the revenue, whereas the second formula has one. Can you not use two curly brackets criteria in excel formulas?

I can't find any information on the internet about using multiple criteria brackets in formulas.
 
Hi ,

Your formula :

=SUMPRODUCT(SIGN((njdata[StoreType]={"SIS","SIS M","SIS L","SIS P"})*(njdata[Season]={"AW16","SS16","AW15"}))*njdata[revenue])

can be split up into the different coloured components.

Let us assume your table has 100 rows of data.

The first component , coloured in RED , will generate a matrix of values which is 100 rows by 4 columns.

The second component , coloured in BLUE , will generate a matrix of values which is 100 rows by 3 columns.

Multiplying these two will generate a matrix with a column of error values , as a result of which the entire formula will return an error result.

When you split up the second component into 3 separate sections , as in the second formula , you are generating a column vector of 100 rows and 1 column ; adding all 3 sections results in a column vector having 100 rows and 1 column.

Multiplying a 100 row by 4 column matrix by a 100 row by 1 column vector will not generate any error.

What you can do is convert each of the matrices , resulting from each component , into a column vector , as follows :

=SUMPRODUCT(MMULT(--(njdata[StoreType]={"SIS","SIS M","SIS L","SIS P"}) , {1;1;1;1}) * MMULT(--(njdata[Season]={"AW16","SS16","AW15"}) , {1;1;1}) * njdata[revenue])

Narayan
 
Thanks for the solid explanation and answers. Much appreciated :)

I did not know about the matrices and vectors. Prior I have worked around above issue, but knowing this will be very helpful.

Also, very nice with the color touch to the solutions/explanations! I'll use that from now on.
 
Back
Top