INDEX/COLLECT Q: Running into issues pulling data from another sheet based on 2 criteria

Maggie Collins

I am attempting to pull attendee numbers from a master grid to a summary sheet. Our master grid has upwards of 30 columns and is used as a central repository for event-related information across multiple years. I have been tasked with compiling a dashboard for our senior/executive leadership to showcase the total # of attendees based on a variety of criteria (including department, event date, level of involvement, etc.).

I'd like to pull the attendees to the summary sheet based on two criteria: Event Name and Start Date. I've tried using the following INDEX/COLLECT formula, but it keeps showing an #INVALID VALUE error:

=INDEX(COLLECT({STAT: CONF Total Attendees}, {Event Name}, [Event Name]@row, {Start Date}, [Start Date]@row), 1)

I've included a screenshot of sample data that I'm trying to pull into a new sheet. The yellow column indicates the values that I want to populate in the summary sheet.

Any help would be greatly appreciated. Thank you in advance!

Smartsheet_Index Collect Screenshot.png

MichaelTCA

@Maggie Collins Awesome. Then my next question is, do the column properties match?

Dates can cause errors if they aren't the same.

And just to clarify, are the other two columns text/number?

MichaelTCA

Hello @Maggie Collins

If you are using the @row option, the function is going to look at your summary sheet and use that value to reference in the formula. According to your formula, you would have to specify what the Event Name and Start Date is in order for it to use that as criteria.

Do you specify which values to reference in the summary sheet?

Using your sample with the function for the first row:

The function will result in 250, but your summary sheet needs to specify that "12/15/23"@row is the starting date and "Conference A"@row is the event. It will only look for those values to match and must be within the summary sheet.

SUMIFS() might be a better function to use.

Maggie Collins

HI @MichaelTCA , thanks for the quick response! Yes, I do reference both of those values in the summary sheet.

MichaelTCA

@Maggie Collins Awesome. Then my next question is, do the column properties match?

Dates can cause errors if they aren't the same.

And just to clarify, are the other two columns text/number?

Maggie Collins

@MichaelTCA - thank you!! I just double-checked the column properties for both sheets and one of the date columns was set at text/number instead of date. The formula worked as soon as I changed it. Thank you so much, not only for helping to answer the questions but also for the reminder to always make sure the column properties match up :)

MichaelTCA

Glad to help! 😀