Forum Discussion

MATEO LARESGOITI's avatar
MATEO LARESGOITI
Copper Contributor
Nov 05, 2018

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

  • Willy Lau's avatar
    Willy Lau
    Nov 08, 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

  • Lorenzo Kim's avatar
    Lorenzo Kim
    Bronze Contributor

    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

    • MATEO LARESGOITI's avatar
      MATEO LARESGOITI
      Copper Contributor

      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: 

      • Willy Lau's avatar
        Willy Lau
        Steel Contributor
        =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

Resources