Forum Discussion

JoeUser2004's avatar
JoeUser2004
Bronze Contributor
Jul 04, 2022

JohnTaylor20 

 

Without trying to understand your formula, you might try (untested):

 

=SUMPRODUCT(ISNUMBER(FIND({"yes, Approved, Accepted, Correct, Done"},

LOWER(Table[comment])))*(YEAR(Table[date])=YEAR(TODAY()))

*(WEEKNUM(Table[date]+0)=WEEKNUM(TODAY())))

 

And if that does not work, change the comma to a semicolon in the array-constant, to wit:  {"yes; Approved; Accepted; Correct; Done"}

 

And more flexibly, enter the strings into a contiguous column or row range, replace the array-constant with the range reference.

 

  • JohnTaylor20's avatar
    JohnTaylor20
    Copper Contributor
    Jul 04, 2022
    =SUMPRODUCT(ISNUMBER(FIND({"yes;approved"},LOWER(Table[comment])))*(YEAR(Table[date])=YEAR(TODAY()))*(WEEKNUM(Table[date]+0)=WEEKNUM(TODAY())))

    it didnt work, the result is 0
    • JoeUser2004's avatar
      JoeUser2004
      Bronze Contributor
      Jul 04, 2022

      JohnTaylor20   wrote:  ``it didnt work, the result is 0``

       

      Sorry to hear that.  Hopefully someone else will have more constructive ideas.  Good luck!

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Jul 04, 2022

      JohnTaylor20 

      Perhaps you mean

      =SUMPRODUCT(--(MMULT( 
     ISNUMBER(FIND({"yes", "approved"},LOWER(Table[comment]))),
     {1;1} ) > 0 )  *
   (YEAR(Table[date])=YEAR(TODAY()))*
   (WEEKNUM(Table[date]+0)=WEEKNUM(TODAY())))

Resources