Forum Discussion

ragomes1972's avatar
ragomes1972
Copper Contributor
Apr 13, 2021

Help with challenging formula

Hello! I need help figuring out how to get a formula to work and was counting on this community's expertise!   I basically have a table with ticket items where one of the columns for each ticket i...
Formulas and Functions
ragomes1972's avatar
ragomes1972
Copper Contributor
Apr 13, 2021

Thanks, Detlef_Lewin!
Sorry, I wasn't clear enough in my previous explanation. What I'm actually trying to figure out is one single formula that gives me the total number of rows that meet the criteria.
I've reviewed the Excel sheet and attached it again for more clarity.
Please let me know if you can help me out.
Much appreciated!

  • ragomes1972's avatar
    ragomes1972
    Copper Contributor
    Apr 14, 2021

    Thanks Detlef_Lewin! But I'm afraid I can't use a Power Query solution due to the fact that my table is overwritten on an hourly basis with data coming from a database. It's always written with the same columns but whatever I had there before in terms of content and formatting gets deleted once the new data comes in.
    The formula I'm looking for would be on a separate tab and as long as there's data to read from it calculates the total number of tickets from the table that matches my criteria. Hence why it needs to be one formula.
    If the formula was done in google sheets before, I must believe there's a way to do it in Excel but since the formula does not translate exactly to Excel, it's been a big challenge.

    The formula I had in Google Sheets before is as follows (not sure if it's helpful at all but at least for reference):
    =arrayformula( sum( iferror( regexmatch( filter(INDIRECT("'JQL - Sprint "& A4 & "'!S2:S"), len(INDIRECT("'JQL - Sprint "& A4 & "'!S2:S")), INDIRECT("'JQL - Sprint "& A4 & "'!B2:B") = "Bug") & ";", textjoin( "|", true, filter(INDIRECT("'JQL - Sprint "& A4 & "'!C2:C"), len(INDIRECT("'JQL - Sprint "& A4 & "'!C2:C")), INDIRECT("'JQL - Sprint "& A4 & "'!B2:B") = "Story") & ";" ) ) + 0 ) ) )

    Let me know your thoughts.
    Thanks again!

    • mtarler's avatar
      mtarler
      Silver Contributor
      Apr 14, 2021

      ragomes1972  It isn't pretty but this formula will give you a helper column to determine if that row meets the criteria:

      =LET(in,E2,lenIN,LEN(in),xploded,SUBSTITUTE(in,";",REPT(" ",lenIN)),n,IFERROR(CEILING.MATH(LEN(xploded)/lenIN,1),0),list,IF(n,TRIM(MID(xploded,SEQUENCE(n,,1,lenIN),lenIN)),""),storys,FILTER(C:C,B:B="Story"),st_true,OR(IFERROR(MATCH(list,storys,0)>0,FALSE)),b_true,AND(B2="Bug",F2="Yes",st_true),--b_true)

      see attached

       

      • ragomes1972's avatar
        ragomes1972
        Copper Contributor
        Apr 14, 2021
        Thanks, mtarler!
        This is great but I'm wondering if there's a way to achieve the result without the use of a helper column or table through a single formula.
        Let me know if you have any thoughts on that.
        I truly appreciate your time to help with this!
    • Detlef_Lewin's avatar
      Detlef_Lewin
      Silver Contributor
      Apr 14, 2021

      ragomes1972 

      But I'm afraid I can't use a Power Query solution due to the fact that my table is overwritten on an hourly basis with data coming from a database. It's always written with the same columns but whatever I had there before in terms of content and formatting gets deleted once the new data comes in.

      Then PQ seems to be the best choice.

       

       

Resources