Help with challenging formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2270526%22%20slang%3D%22en-US%22%3EHelp%20with%20challenging%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270526%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3EI%20need%20help%20figuring%20out%20how%20to%20get%20a%20formula%20to%20work%20and%20was%20counting%20on%20this%20community's%20expertise!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20basically%20have%20a%20table%20with%20ticket%20items%20where%20one%20of%20the%20columns%20for%20each%20ticket%20is%20called%20Linked%20Issues.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20an%20item%20from%20my%20table%20is%20listed%20as%20%22Bug%22%20then%20we%20should%20look%20at%20the%20Linked%20Issues%20column%20and%20see%20if%20any%20of%20those%20comma-separated%20items%20are%20also%20listed%20in%20the%20table%20as%20%22Story%22.%20If%20this%20criterion%20is%20met%20then%20my%20bug%20counts%20as%20one.%20Otherwise%2C%20it's%20a%20zero.%3C%2FP%3E%3CP%3EOne%20last%20criterion%20is%20that%20another%20column%20must%20also%20contain%20a%20%22Yes%22%2C%20otherwise%20this%20Bug%20won't%20be%20accounted%20for%20in%20my%20total.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20a%20file%20with%20an%20example%20and%20a%20detailed%20explanation.%20With%20help%2C%20in%20the%20past%2C%20I've%20made%20this%20work%20in%20Google%20Sheets%20but%20it%20uses%20formulas%20such%20as%20%22regexmatch%22%20and%20%22arrayformula%22%20to%20make%20the%20calculation.%3C%2FP%3E%3CP%3EIt's%20been%20a%20challenge%20to%20solve%20this%20Excel%20so%20any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3EThank%20you%20very%20much!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2270526%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2270642%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20challenging%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2270642%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1024126%22%20target%3D%22_blank%22%3E%40ragomes1972%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3D((B2%3D%22Bug%22)*(F2%3D%22Yes%22)*IFERROR(SUM(--(IF((%24C%242%3A%24C%2416%3DTRANSPOSE(FILTERXML(%22%3CY%3E%3CZ%3E%22%26amp%3BSUBSTITUTE(E2%2C%22%3B%22%2C%22%3C%2FZ%3E%3CZ%3E%22)%26amp%3B%22%3C%2FZ%3E%3C%2FY%3E%22%2C%22%2F%2Fz%22)))%2C%24B%242%3A%24B%2416)%3D%22Story%22))%2C0))%26gt%3B0%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2271190%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20challenging%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2271190%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E!%3CBR%20%2F%3ESorry%2C%20I%20wasn't%20clear%20enough%20in%20my%20previous%20explanation.%20What%20I'm%20actually%20trying%20to%20figure%20out%20is%20one%20single%20formula%20that%20gives%20me%20the%20total%20number%20of%20rows%20that%20meet%20the%20criteria.%3CBR%20%2F%3EI've%20reviewed%20the%20Excel%20sheet%20and%20attached%20it%20again%20for%20more%20clarity.%3CBR%20%2F%3EPlease%20let%20me%20know%20if%20you%20can%20help%20me%20out.%3CBR%20%2F%3EMuch%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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 is called Linked Issues. 

If an item from my table is listed as "Bug" then we should look at the Linked Issues column and see if any of those comma-separated items are also listed in the table as "Story". If this criterion is met then my bug counts as one. Otherwise, it's a zero.

One last criterion is that another column must also contain a "Yes", otherwise this Bug won't be accounted for in my total.

 

I've attached a file with an example and a detailed explanation. With help, in the past, I've made this work in Google Sheets but it uses formulas such as "regexmatch" and "arrayformula" to make the calculation.

It's been a challenge to solve this Excel so any help would be greatly appreciated!

Thank you very much!

 

12 Replies

@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

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 

In that case I would suggest a Power Query solution.

 

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!

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

 

 

@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

 

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!

Here's why I can't use a helper column - I've attached two screenshots that show:

  • tabs for each Sprint that contains data being pulled from a Jira database. For example: "JQL - Sprint 27" pulls data for sprint 27 from Jira
  • The "Unplanned Work" tab:
    • Notice how this tab presents a table where each role is looking to pull data from its specific Sprint tab (the ones that automatically pull data from Jira database) to come up with a total number of bug tickets that are story (feature) related.

So what I need is a formula that goes on column F for each role, looks at the respective Sprint tab and presents the total of tickets that match the criteria.

 

This is why I can't use a helper table or a helper column and need a single formula to do the job:

  • the data from the sprints tab gets completely overwritten every hour and any previous data gets erased - so using a helper table or column here wouldn't work because that would be overwritten.

I hope this clarifies the scope of my question. The sample sheet I provided in this thread was a short version of the original one, which may lead to think I can make use of other Excel features such as Power Query, helper tables, etc. when all I need is a single formula. 

Don't want to seem demanding or anything but thought I'd clarify the question to eliminate confusion and not waste your time on the wrong path.

 

Thank you!

 

 

I get that the table gets overwritten each time, but the helper column could be on a completely different sheet/tab. That said, I'm sure there is some way to make it into a single formula, it would just take some time for me to figure it out (others here may do it much faster)
Thanks, @mtarler.
Yeah, I understand the suggestion but I'd have to manually create a user column for every new sprint, which defeats the purpose of making it automated. Besides, wherever those columns would sit within the spreadsheet, imagine having dozens of helper columns there that keep growing indefinitely - especially because the spreadsheet is already pretty large.
I can't believe the formula was so easily created in Google Sheets thou. Hopefully, I'll find an equivalent single formula in Excel like I had in Google Sheets...

@ragomes1972 

The idea of the solution shown in the image is to filter the dataset and than count the number of rows returned

image.png

The formula is specific to Excel 365 but I have avoided the most recent functionality on beta release.

= LET(
  links, ISNUMBER(SEARCH(TRANSPOSE(Ticket&";"), linked&";")),
  linkStatus, N((Issue_Type)="Story"),
  eachLink, N(ISTEXT(Ticket)),
  linkFilter, MMULT(links*1, linkStatus)>0,
  bugFilter, Issue_Type="Bug",
  commentFilter, Sprint_Commit="Yes",
  combined, bugFilter*commentFilter*linkFilter,
  required, FILTER(Table1, combined),
  ROWS(required))

. The first steps analyse the linked issues and gather the results as a matrix multiplication.  Later the entire table is filtered and could be output to the sheet if required (it requires only a single cell for the formula, just as the count, but will spill to an adjacent range).

I notice that the 'eachLink' line is an unused left-over. I could try to edit the post but that tends to create a mess and I think this forum does not allow posts to be deleted for rework.

I would also note that the Excel preferred option for importing data is via PowerQuery which supports data manipulation to clean and combine data before it even appears on the spreadsheet.