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
Detlef_Lewin's avatar
Detlef_Lewin
Silver Contributor
Apr 13, 2021

ragomes1972 

Try this:

=((B2="Bug")*(F2="Yes")*IFERROR(SUM(--(IF(($C$2:$C$16=TRANSPOSE(FILTERXML("<y><z>"&SUBSTITUTE(E2,";","</z><z>")&"</z></y>","//z"))),$B$2:$B$16)="Story")),0))>0
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

         

Resources