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)

 

 

And my second tab have this structure. For one name ID (Bob, for example) all datas where I need to do my search will always be between the "Bob" lign and the "End Bob" line.

 

 

 

How to detect that "between the Bob lign and the End Bob lign, sum all prices column where the Date column is in January".

 

Thanks a lot for anyone who can guide me on this.

 

excel

4 Replies

  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Jan 18, 2022

    Etienne_D 

    =SUMPRODUCT(((MONTH(INDIRECT("Feuil"&ROW(2:2)&"!C"&B1+1&":C"&B2-"1")))=B3)*INDIRECT("Feuil"&ROW(2:2)&"!D"&B1+1&":D"&B2-"1"))

     

    This formula works in my spreadsheet as shown in the attached file. The name can be selected in A1 and in cell A7 the sum for e.g. january is calculated. The month can be dynamically chosen in cell B3.

    • 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