CODEWITHSUNDEEP
arraysexcelexcel-formula
user3666237
user3666237

Reputation: 153

Sumproduct with multiple criteria

I'm currently using Sum Product to rank year over year data from largest to smallest. My formula in Column Z is SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1 using the data in column Y. The year over year data is in columns W and X. I want to write a formula that does the same thing as far as ranking the data from largest to smallest but I want to exclude the points that may be zero in column W and X. Because if there's no prior or latter data points--meaning that they're zero--I want them excluded.

For example

Column W | Column X | Column Y | Column Z

May14    |  May13   |   (YOY)  |   Rank

3228     |    0     |   3228   |    1
1324     |   1309   |    15    |    2

enter image description here

Upvotes: 0

Views: 362

Answers (2)

user3666237
user3666237

Reputation: 153

Nevermind guys What I did if just created the IF outlier in the YOY calculation. So in column Y, IF(OR(W6=0,X6=0,),0,W6-X6). By just making the value 0 from that point the sumproduct is able to exclude that outlier.

Upvotes: 1

TMH8885
TMH8885

Reputation: 888

What you could do is add an IF statement to your formula in column Z:
IF(AND(W5=0,X5=0),0,SUMPRODUCT(--(ABS($Y$5:$Y$165)>ABS(Y5)))+1)

This will make the value in column Z equal to 0 if both W and X are 0. Then you can easily filter these 0s out.

Upvotes: 0

Related Questions