cunningAce
Board Regular
- Joined
- Dec 21, 2017
- Messages
- 91
- Office Version
- 365
- Platform
- Windows
I am working with the following sample data.
<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>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
Product Code | Category | 1 | 2 | 3 | 4 | 5 | rank |
123 | Low | 10 | 20 | 30 | 40 | 50 | 2 |
456 | Low | 5 | 10 | 15 | 20 | 25 | 3 |
789 | Med | 2 | 4 | 6 | 8 | 10 | |
234 | Med | 3 | 6 | 9 | 12 | 15 | |
567 | High | 100 | 200 | 300 | 400 | 500 | |
345 | High | 20 | 40 | 60 | 80 | 100 | |
678 | Low | 15 | 30 | 45 | 60 | 75 | 1 |
Week No | 1 | Category | Low | ||||
<colgroup><col><col><col span="6"></colgroup><tbody>
</tbody>
=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