Rank IF / Sum product - Multiple Criteria

[cunningAce]

I am working with the following sample data.


[TABLE="width: 536"]
<colgroup><col><col><col span="6"></colgroup><tbody>[TR]
[TD]Product Code[/TD]
[TD]Category[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]rank[/TD]
[/TR]
[TR]
[TD]123[/TD]
[TD]Low[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[TD]50[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]456[/TD]
[TD]Low[/TD]
[TD]5[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]20[/TD]
[TD]25[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]789[/TD]
[TD]Med[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]234[/TD]
[TD]Med[/TD]
[TD]3[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]12[/TD]
[TD]15[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]567[/TD]
[TD]High[/TD]
[TD]100[/TD]
[TD]200[/TD]
[TD]300[/TD]
[TD]400[/TD]
[TD]500[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]345[/TD]
[TD]High[/TD]
[TD]20[/TD]
[TD]40[/TD]
[TD]60[/TD]
[TD]80[/TD]
[TD]100[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]678[/TD]
[TD]Low[/TD]
[TD]15[/TD]
[TD]30[/TD]
[TD]45[/TD]
[TD]60[/TD]
[TD]75[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Week No[/TD]
[TD]1[/TD]
[TD]Category[/TD]
[TD]Low[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
I have a sum product formula as below acting as a 'rank if' function based on the criteria from drop down list in D12, 'Category'

=IF(B2=$D$12,SUMPRODUCT(--($B$2:$B$8=$D$12),--(C2<$C$2:$C$8))+1,"")

This works fine but id like to add a second criteria, 'Week No' another drop down list in B12.

How would I update the formula; I assume the bit in red, so the rank column is determined by both category and weeks number selected from the drop down lists.

Thanks in advance

 

[DRSteele]

This just might do it.

=IF(B2=$D$12,SUMPRODUCT(--($B$2:$B$8=$D$12),--(INDEX(C2:G2,,$B$12) < INDEX($C$2:$G$8,,$B$12)))+1,"")

 

[cunningAce]

Thank you for your answer; works perfectly in this example
I am trying to apply this to a data set though where the Week numbers don't necessarily correspond to the column number you are passing in the index function that you suggested to use.
The first column of sales data for example could actually be labelled Week 25, in which case this formula doesn't work.
Can this be implemented.

Thanks again for your help already

 

[stunnrock]

Try this:

 =IF(B2=$D$12,SUMPRODUCT(($B$2:$B$8=$D$12)*(OFFSET(C2,,$B$12-1)<OFFSET($C$2:$C$8,,$B$12-1)))+1,"")

<offset($c$2:$c$8,,$b$12-1)))+1,"")[ code]<offset($c$2:$c$8,,$b$12-1)))+1,"")[="" code]<offset($c$2:$c$8,,$b$12-1)))+1,"")<offset($c$2:$c$8,,$b$12-1)))+1,"")<offset($c$2:$c$8,,$b$12-1)))+1,"")[="" code]<offset($c$2:$c$8,,$b$12-1)))+1,"")<offset($c$2:$c$8,,$b$12-1)))+1,"")<="" html=""></offset($c$2:$c$8,,$b$12-1)))+1,"")[>

 

[cunningAce]

Thanks for the response,

This returns the correct category and week number as the selections made but the rank returned is '1' for all
Might be a simple fix but I'm not great with the offset function

Thanks

 

[stunnrock]

Odd, it works perfectly with your sample data. I did have some trouble getting this forum to see my formula correctly (took several attempts before it would show anything after the less-than sign), so I wonder if you got it all. It should have a less-than symbol after
=IF(B2=$D$12,SUMPRODUCT(($B$2:$B$8=$D$12)*(OFFSET(C2,,$B$12-1)

and then:

OFFSET($C$2:$C$8,,$B$12-1)))+1,"")

 

[cunningAce]

I have all the formula correctly.
If you change the week numbers from 1-5 to 20-24 instead the formula returns all 1's for the selections selected from the B12 and D12, it returns a 1 for each, rather than ranking them.

Thanks

 

[stunnrock]

Ah, I see.

=IF(B2=$D$12,SUMPRODUCT(($B$2:$B$8=$D$12)*(OFFSET(C2,,MATCH($B$12,$C$1:$G$1,0)-1)<OFFSET($C$2:$C$8,,MATCH($B$12,$C$1:$G$1,0)-1)))+1,"")

 

[cunningAce]

Perfect, thank you so much!