INDEX/COLLECT Q: Running into issues pulling data from another sheet based on 2 criteria
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!
Best Answer
-
@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?
Answers
-
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.
-
HI @MichaelTCA , thanks for the quick response! Yes, I do reference both of those values in the summary sheet.
-
@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 - 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 :)
-
Glad to help! 😀
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!