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