Help converting Google Sheets Challenging Formula

[ragomes]

Hello!

I need help figuring out how to get a formula I had working on Google Sheets to now work in Excel and was counting on this community's expertise, please!

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 a "Bug" then we should look at the Linked Issues column for that item and see if any of those comma-separated items are also listed in the table as "Story" in the same table. If this criterion is met then my bug counts as one in my grand total.

One last criterion is that another column must also contain a "Yes". Otherwise, the 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.
Here's what it looked like:
=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 ) ) )

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

Thank you very much!

 

[Norie]

Perhaps this will get you started.

Put this formula in G2 and copy down.

=IF(AND(B2="Bug", F2="Yes", E2<>""), TRANSPOSE(INDEX($B$2:$B$16,MATCH(FILTERXML("<y><z>"&SUBSTITUTE(E2,";","</z><z>")&"</z></y>","//z"), $C$2:$C$16,0))), "")

It will return the issue type for each linked issue for bugs that have been committed.

Those will be returned in columns from G onwards.

I'm still trying to figure out how to check whether a 'story' issue has been returned - I've tried various things, e.g. COUNTIF, but Excel keeps complaining.

Actually, I think I've got it.

This formula will return TRUE if all your criteria are met.

=IF(AND(B2="Bug", F2="Yes", E2<>""), OR(TRANSPOSE(INDEX($B$2:$B$16,MATCH(FILTERXML("<y><z>"&SUBSTITUTE(E2,";","</z><z>")&"</z></y>","//z"), $C$2:$C$16,0)))="Story"), "")

 

[ragomes]

Perhaps this will get you started.

Put this formula in G2 and copy down.

=IF(AND(B2="Bug", F2="Yes", E2<>""), TRANSPOSE(INDEX($B$2:$B$16,MATCH(FILTERXML("<y><z>"&SUBSTITUTE(E2,";","</z><z>")&"</z></y>","//z"), $C$2:$C$16,0))), "")

It will return the issue type for each linked issue for bugs that have been committed.

Those will be returned in columns from G onwards.

I'm still trying to figure out how to check whether a 'story' issue has been returned - I've tried various things, e.g. COUNTIF, but Excel keeps complaining.

Actually, I think I've got it.

This formula will return TRUE if all your criteria are met.

=IF(AND(B2="Bug", F2="Yes", E2<>""), OR(TRANSPOSE(INDEX($B$2:$B$16,MATCH(FILTERXML("<y><z>"&SUBSTITUTE(E2,";","</z><z>")&"</z></y>","//z"), $C$2:$C$16,0)))="Story"), "")

Thanks, @Norie ! Much appreciated!
My table actually comes from a tickets database so the table gets automatically overwritten with an update on an hourly basis, and with that, any formulas or formatting that I have there gets erased as well. Hence why I'm looking for a single formula (like I had on Google Sheets) that looks at the entire table and gives me the total number of tickets from the table that match the criteria.
I know it's a bit challenging but this is a really good start! Thanks again for taking the time to look into this!
Let me know if you have any additional thoughts.

 

[ragomes]

Thanks, @Norie ! Much appreciated!
My table actually comes from a tickets database so the table gets automatically overwritten with an update on an hourly basis, and with that, any formulas or formatting that I have there gets erased as well. Hence why I'm looking for a single formula (like I had on Google Sheets) that looks at the entire table and gives me the total number of tickets from the table that match the criteria.
I know it's a bit challenging but this is a really good start! Thanks again for taking the time to look into this!
Let me know if you have any additional thoughts.

Forgot to mention that the single formula I'm looking to have will be on a separate tab so it doesn't get scrubbed out when the new data with tickets comes in.
Thanks1

 

[Norie]

You didn't actually say what the formula you posted returned and I couldn't test it without data.

There's probably a way to convert what I posted to simply give a count and be dynamic rows-wise, I'll see what I can come up with.

P.S. If I was doing this for myself, in either Sheets or Excel, I'd probably create a user-defined function - in Sheets with Google Apps Script and in Excel with VBA.

 

[kweaver]

Modify that great solution:

=IF(AND(Data!B2="Bug", Data!F2="Yes", Data!E2<>""), OR(TRANSPOSE(INDEX(Data!$B$2:$B$16,MATCH(FILTERXML("<y><z>"&SUBSTITUTE(Data!E2,";","</z><z>")&"</z></y>","//z"), Data!$C$2:$C$16,0)))="Story"), "")

Then, use

=COUNTIF(A:A,TRUE)

to get your solution, assuming the formula is in the A column and filled down.

 

[ragomes]

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!

 

[Norie]

Who mentioned helper columns/tables?

P.S. I don't seem to see any screen shots, did you have trouble attaching them?

 

[ragomes]

Hopefully, the attachments will go through now.
Sorry, a bit out of context but I was just trying to make a point that I can't use a helper column or table but instead, it needs to be a single formula (see screenshots attached for clarification).
Thanks!