Index Collect

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
    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:


    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:


    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. 🙂

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!