Forum Discussion
Solved
Excel Challenge HELP - If you want a challenge with Date, List, Filtering...
I have a list of products sold and the dates they were sold. The first 3 products sold every month are given away for free. So I need in a single cell (if possible), to calculate if the product is on...
=IF(OR(SUMPRODUCT(TEXT($B2,"yyyymm")=TEXT($B$1:$B$51,"yyyymm"))*($B2>$B$2:$B$51))+1)={1,2,3}),0,$C2)$B$2:$B$51 is supposed for the sold date with time.
reference : excel rank by group
Thanks for your reply but it still doesn't seem to do the trick. It's working but it gives me only the first three products within all the list, not the first three products of every month. Here is a screen-shot:
=IF(OR(SUMPRODUCT(TEXT($B2,"yyyymm")=TEXT($B$1:$B$51,"yyyymm"))*($B2>$B$2:$B$51))+1)={1,2,3}),0,$C2)$B$2:$B$51 is supposed for the sold date with time.
reference : excel rank by group
- Perfect! Thank you it worked perfectly! I actually altered it a bit and ended up with something a little simpler:
=IF((SUMPRODUCT((TEXT($D$6:$D$999,"yyyymm")=TEXT($D6,"yyyymm"))*(D6>$D$6:$D$999))+1)<=3,0,$C2)- nice formula....
congratz - yes, it is better. Just in case you want to let it be free if the item is the xth, yth, zth, you may use my approach. :)
