Index Collect

Ed B.

I have a data sheet called GC Budgets Tracking Sheet with the following columns and data.

On sheet #2 I would like to collect the $/sf individually for each project a specific contractor proposed on. In column 2 of that sheet I am entering the Contractors name, for instance "Builders Inc". I am using the following formula, to grab the first instance for "Builders Inc" and then on the next row changing the "1" to a "2" to grab the 2nd instance and so on.

=INDEX(COLLECT({GC Budgets Tracking Sheet $/SF}, {GC Budgets Tracking Sheet Contractor}, $[Column2]@row), 1)

This works but I have to manually enter all 3 instances for "Builders Inc" for the example above, and would only have 2 instances for "Constructors", and 2 for "Anderson GC", etc. Easy for the above but I have about 60-70 entries.

I have two questions:

1. Is there a way to perform this but have it automatically add a row ever time you add another "Builders Inc" instance?
2. I would like to also only grab data that is within the past 6 months, but can't figure out how to add an IF statement in the formula?

Help on either of the above would be appreciated.

Nick Korna

Hi @Ed B.,

You should be able to do this with a SUMIFS and some cross sheet references:

If your sheet #2 has the contractors listed in Column2:

=SUMIFS({$/SF},{Contractor},[Column2]@row)

You can then expand this to include the past 6 months - there are several ways of doing this, but an example (a somewhat quick and dirty one) would be:

=SUMIFS({$/SF},{Contractor},[Column2]@row,{Date},>TODAY(-180))

If you want the past 6 months to be a bit more specific, then that TODAY portion can be replaced with something else.

Hope this helps, but if you've any problems/questions, then just post. 🙂