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...

excel

Excel on Mac

Formulas and Functions

Willy Lau
Nov 07, 2018
=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

MATEO LARESGOITI

Copper Contributor

Nov 08, 2018

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)

Lorenzo Kim

Bronze Contributor

Nov 08, 2018

nice formula....
congratz

Forum Discussion

Excel Challenge HELP - If you want a challenge with Date, List, Filtering...