Apr 13 2021 09:39 AM - edited Apr 13 2021 04:15 PM
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!
Apr 13 2021 10:39 AM
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
Apr 13 2021 04:15 PM - edited Apr 13 2021 08:38 PM
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!
Apr 13 2021 09:04 PM
Apr 14 2021 09:58 AM - edited Apr 14 2021 09:58 AM
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!
Apr 14 2021 11:54 AM
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.
Apr 14 2021 02:25 PM
@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
Apr 14 2021 02:55 PM
Apr 14 2021 04:18 PM
Here's why I can't use a helper column - I've attached two screenshots that show:
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:
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!
Apr 14 2021 05:20 PM
Apr 15 2021 02:46 PM
Apr 16 2021 02:33 AM
The idea of the solution shown in the image is to filter the dataset and than count the number of rows returned
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).
Apr 16 2021 02:52 AM