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!

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.

Help with challenging formula | Microsoft Community Hub

12 Replies

PeterBartholomew1
Silver Contributor
Apr 16, 2021
ragomes1972
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).
Excel-formula-help.xlsx15 KB
- PeterBartholomew1
  Silver Contributor
  Apr 16, 2021
  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.
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
  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!
  - Detlef_Lewin
    Silver Contributor
    Apr 14, 2021
    ragomes1972
    In that case I would suggest a Power Query solution.
    
    techcommunity_2271190M96286.xlsx27 KB

Forum Discussion

Help with challenging formula

12 Replies

Resources