INDEX COLLECT

1970Sarah

Hello

I have an 'Internal Resource Rates' sheet set up which shows Service Line and Role: on here I have a UK and US rate for each of these roles:

My resourcing sheet - below - looks at the above via a formula: the 'Internal Rate' value changes based on the 'Currency' value:

I have created this formula and it returns the US $ value correctly:

=IFERROR(IF(Location@row = "US", (INDEX(COLLECT({Internal Resource Rates | US $}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row), 1))), "")

When I add (what I think) should be an additional argument to return the UK value when the locations isnt US, I get an error:

=IFERROR(IF(Location@row = "US", (INDEX(COLLECT({Internal Resource Rates | US $}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row), (INDEX(COLLECT({Internal Resource Rates | UK £}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row), 1)))), "")

Am I missing the obvious?! I'm relatively new to formulas btw!

Kelly Moore

Hey @1970Sarah

When you added the additional Index/Collect, you needed to add an additional row index parameter (the 1 at the end of the INDEX clause) to complete the new Index/Collect

Try this

=IFERROR(IF(Location@row = "US", INDEX(COLLECT({Internal Resource Rates | US $}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row),1), INDEX(COLLECT({Internal Resource Rates | UK £}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row), 1)), "")

Does this work for you?

Kelly

Kelly Moore

Hey @1970Sarah

When you added the additional Index/Collect, you needed to add an additional row index parameter (the 1 at the end of the INDEX clause) to complete the new Index/Collect

Try this

=IFERROR(IF(Location@row = "US", INDEX(COLLECT({Internal Resource Rates | US $}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row),1), INDEX(COLLECT({Internal Resource Rates | UK £}, {Internal Resource Rates | Service Line}, CONTAINS([Service Line]@row, @cell), {Internal Resource Rates | Internal Role}, [Internal Role]@row), 1)), "")

Does this work for you?

Kelly

1970Sarah

Kelly - thankyou! That has worked perfectly! Thank you very much for your help :)

Ann Hannan

Hi @Kelly Moore,

I've been working on a similar formula the the one you helped Sarah with but I can't seem to get it right. Here is where I am so far:

=IF(Ancestors@row = "1", INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Transaction Helper ID Range}, CONTAINS([QLICI ID]@row, @cell), {Bill Group Range}, CONTAINS([Bill Group]@row, @cell), 1)), “”)

With this formula I am getting an "Incorrect Argument" returned.

I want to return the value from QALICB Servs1-5 Bill Amt Range from Sheet 1 based on a match of QLICI ID and Bill Group - two criteria. I also need to have an IF statement that indicates what column should be used based on Ancestors. If Ancestors = 1, I will compare the range {Transaction Helper ID Range} to QLICI ID, but if it is Ancestors = 2, I will compare the {Roles QLICI Range} to QLICI ID.

The formula above is my attempt to include the IF statement and the two criteria for the Index(Collect). Once correct, this formula needs to be modified to include the addition IF statement for Ancestors@row = 2. If Ancestors does not equal 1 or 2, the formula can return "".

I'd greatly appreciate your help in getting this formula to work! I just have not been able to get it on my own.

Thanks!

Ann

Ann Hannan

Hi @Genevieve P. - You have been so kind to help me with past formula woes. Any insight on my current issue with this IF / INDEX(COLLECT) / CONTAINS combination?

Thank you!

Ann @Ann Hannan

Kelly Moore

Hey @Ann Hannan

Try this.

=IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Transaction Helper ID Range}, CONTAINS([QLICI ID]@row, @cell), {Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), “”)

I moved a parenthesis to close off the COLLECT function.

You will add the second IF statement where the double quotes are now. If you need any help, if you give me the second IF, I can add it to the formula.

Kelly

Ann Hannan

Thank you so much, Kelly! That worked. Those pesky parens. I knew it was something like that, but I'd hit my limit. I truly appreciate your quick response.

Ann

Ann Hannan

Hi @Kelly Moore ,

I hope you don' t mind a follow-up question. I've made a lot of progress with your help and now my formula looks like this:

=IF([Year Group]@row = "Years 1-5", (IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1)))), "n/a")

I've realized though that I would love to have a SUM(CHILDREN()) on the rows where Ancestors=0.

For these rows, the Year Group will be blank, but that is my first condition. I'm not sure where to add the Sum function. I thought it should apply to the very first IF statement but maybe I have them in the wrong order. I'd like it to work out like this:

If Ancestors = 0 then Sum(Children)

If Ancestors = 1 and Year Group = Years 1-5 - then Index Collect with Transaction ID Range, if not then "n/a"

If Ancestors = 2 and Year Group = Years 1-5 - then Index Collect with QLICI ID Range, if not then "n/a"

You'll see above that since Years 1-5 was a shared criteria, I added it first. Maybe that won't work if I want something different for Ancestors=0.

Again I really appreciate your solutions to these problems! It seems like there should be just a simple way to stick that Sum in there but where?!?!?

Thanks!

Ann

Kelly Moore

Hey Ann

Happy to help. I have a few questions for clarity. Is it possible for a screenshot- this always helps the community. When you say you want the Children, is this the equivalent to the Collect criteria in your Level-1 IF statement? Or is it the sum of both Level 1 and Level 2? I'm assuming you want the sum of your {QALICB Servs1-5 Bill Amt Range}?

Here's the equation with both 1 and 2 criteria. Does this give you what you need?

=IFERROR(IF(Ancestors@row = 0, SUM(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell))), IF([Year Group]@row = "Years 1-5", IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1))), "n/a")), "n/a")

Kelly

Ann Hannan

Hi @Kelly Moore,

Thanks for your reply. When I tried the formula, it returned a value of $0.00 for the top level parent - Ancestor=0.

Currently the 1st level Child already includes a Sum of its Children which is coming from the other table. Therefore, I only want to Sum the "direct" children (not grandchildren :)) of the Ancestor=0 rows.

Here is a screenshot. The blue shaded rows should include a Sum of just the gray shaded rows.

Does that help?

Thanks,

Ann

Kelly Moore

Aah. I wasn't sure if the hierarchy was built on your destination sheet or just on the source sheet.

=IF(Ancestors@row = 0, SUM(CHILDREN([Bill QALICB Servs 1-5]@row)), IF([Year Group]@row = "Years 1-5", (IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs1-5 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1)))), "n/a"))

Does this work for you?

Kelly

Ann Hannan

Thank you, @Kelly Moore !

For a cleaner design, I adjusted the sheet since my last question. The formula no longer needs to check to see if the [Year Group]@row = "Years 1-5". It only needs to match the QLICI ID and the Bill Group for the Index(Collect). I tried to adjust the formula you sent as follows:

=IFERROR(IF(Ancestors@row = 0, SUM(CHILDREN([Bill QALICB Servs 1-5]@row)), IF(Ancestors@row = 1, INDEX(COLLECT({QALICB Servs 1-5 Bill Amt Range}, {Roles Transaction ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), IF(Ancestors@row = 2, INDEX(COLLECT({QALICB Servs 1-5 Bill Amt Range}, {Roles QLICI ID Range}, [QLICI ID]@row, {QALICB Servs 1-5 Bill Group Range}, CONTAINS([Bill Group]@row, @cell)), 1), "")), "n/a"))

Unfortunately, it didn't work. I took the formula that I had working for Ancestors 1 and 2, and added the IF / SUM statement at the front. It is retuning "BLOCKED" for Ancestors 0 and 1, and "INCORRECT ARGUMENT" for Ancestors 2. All the references are the same and were working previously.

I'll keep trying! Please let me know if you see a easy fix!

Thanks,

Ann

Ann Hannan

Nevermind, @Kelly Moore !

I found the out of place parentheses! Formula is working now!!!

Thank you so much for your help. With advisors like you this is fun instead of infuriating. :)

Kelly Moore

Let's swap the Sum for a SUMIFS

IF(Ancestors@row = 0, SUMIFS(CHILDREN([Bill QALICB Servs 1-5]@row, CHILDREN([Bill QALICB Servs 1-5]@row, ISNUMBER(@cell)))

Also, check my parentheses. Follow the color pattern of the parens in the formula. Or share a screenshot of the colored formula.

Kelly Moore

phew! So glad it's working.