Help converting Google Sheets Challenging Formula

ragomes

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Attachments

  • Screen Shot 2021-04-14 at 10.31.20 AM.png
    Screen Shot 2021-04-14 at 10.31.20 AM.png
    133.3 KB · Views: 7

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Perhaps this will get you started.

Put this formula in G2 and copy down.
Excel Formula:
=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.
Excel Formula:
=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

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Perhaps this will get you started.

Put this formula in G2 and copy down.
Excel Formula:
=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.
Excel Formula:
=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

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
May 8, 2018
Messages
1,769
Office Version
  1. 365
  2. 2010
Modify that great solution:

Code:
=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
Code:
=COUNTIF(A:A,TRUE)
to get your solution, assuming the formula is in the A column and filled down.
 

ragomes

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Who mentioned helper columns/tables?

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

ragomes

New Member
Joined
Apr 14, 2021
Messages
6
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,727
Messages
5,638,025
Members
416,999
Latest member
smulttjukken

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top