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

[MATEO LARESGOITI]

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 one of the first 3 and return a value of 0 if true.

Im trying to do this within one cell (I'm trying to avoid additional lists)

*Keep in mind there are many months and years

[#2]

try:
Column A is the "DATE", Column B is the "PRODUCT" and Column C is the "VALUE"
add Column D as "ADJUSTED VALUE"
in Cell D2 - paste formula below and copy down to suit:
=IFERROR(IF(OR(SMALL($A$2:$A$999,1)=A2,SMALL($A$2:$A$999,2)=A2,SMALL($A$2:$A$999,3)=A2),"",C2),"")

where cell A2 is the start of the month
and cell A999 is the end of the month

HTH

[#3]

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: 

[#4]

try sorting the date then-
change the first cell (A2) to the cell of the first day of the next month and (A999) to the last day of that month and so on...

[#5]

pls see attached file

thanks..

[#6]

I have a new approach.

You must save the file as Macro Enabled (.xlsm)

pls see attached file.

the column for DATE will be sorted and the top 3 value changed to zero.

press ALT F8 then run the Macro

hope this can help.

thanks..

[#7]

The problem can be solved if there is a function "RANKIF". I found it very complicated, especially no additional columns is going to add. You may refer to the following page:

https://contexturesblog.com/archives/2017/03/09/excel-rank-formula-example/

Once you developed the "RANKIF" function, you can simply use an "IF" to give the value 0 or other values. 

[#8]

Mr. Chan
Thank you for the info...

[#9 · Accepted solution]

=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

[#10]

Thank you! It worked perfectly

[#11]

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)

[#12]

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

[#13]

nice formula....
congratz