Forum Discussion
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...
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).
- 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.