Forum Discussion

Etienne_D's avatar
Etienne_D
Copper Contributor
Jan 18, 2022

Search between two text cells

I'm trying to do the sum of all prices where the date in un January for each name ID that I have in an other tab.   Basically, on my first tab, I have all names ID (Bob, James, Lucas)     ...
excel
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Jan 18, 2022

Etienne_D 

On which version/platform of Excel you are?

  • Etienne_D's avatar
    Etienne_D
    Copper Contributor
    Jan 18, 2022
    Hello Sergei,

    I'm using the Microsoft® Excel® pour Microsoft 365 MSO (Version 2112) 32 bits
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Jan 18, 2022

      Etienne_D 

      For the source data in Sheet2

      result as

      could be generated with

      =IFERROR(
   LET(
      start, XMATCH(A2,Sheet2!A:A) + 1,
      end,   XMATCH("end " &  A2, Sheet2!A:A) - 1,
      month, TEXT( INDEX( Sheet2!C:C, start ):INDEX( Sheet2!C:C, end ), "mmm"),
      price, INDEX( Sheet2!D:D, start ):INDEX( Sheet2!D:D, end ),
      SUM( FILTER( price, month =   $B$1 ) )
  ), "no data" )

      Please check in attached.

Resources