Index Match Formula help needed
I'm currently trying to calculate the fuel consumption for each truck by date by using using an Index Match formula as shown below
=INDEX(Odometer:Odometer, MATCH(MAX(COLLECT(Created:Created, Created:Created, @cell < Created@row, Truck:Truck, Truck@row)), Created:Created, 0), 0)
This formula is just the part where I am trying to get the odometer reading from the same truck the last time they filled up. However, as you will see in the screenshots it returns repeated values. So I changed the formula and added a column to see which trucks it was constantly repeating and as you will see in the screenshots, the error does lies with the trucks repeating themselves. It's constantly truck 14 and 16 repeating itself.
Could someone kindly help me fix this issue please?
Also for my formula could you help write a formula with the "Date" heading too? As whenever I try change the "Created" column to the "Date" column it says its "unparseable".
Best Answer
-
Hi Rainier,
Happy to help, calculate the fuel consumption for each truck by date. From the description provided, I believe you may be able to achieve your desired goal utilizing a SUMIFS function instead given the values desired to calculate are numeric. Utilizing a SUMIFS function you can add the multiple desired criteria to pull the most recent date and sum all relative numeric values.
SUMIFS: https://help.smartsheet.com/function/sumifs
If you need to visually display the rows text values for a specific date and need it to update as we move forward in time, based on your description, it may be simpler to utilize a Report to achieve. This Help Center article outlines in further detail Reports: https://help.smartsheet.com/articles/522214-creating-reports
If you are unable to produce the desired result after performing the above or if I have misunderstood the request, I think it would be best for us to set aside some time to have a screen share session to try to resolve the issue. Please connect with us utilizing this Support Form: https://help.smartsheet.com/contact
Please provide the best number you can be reached at and a Date/Time that would work best for you?
Note: Ensure to include your desired time zone.
Have a wonderful day. Thank you for contacting Smartsheet Support.
Cheers,
Eric
Smartsheet Technical Support
Answers
-
Hi Rainier,
Happy to help, calculate the fuel consumption for each truck by date. From the description provided, I believe you may be able to achieve your desired goal utilizing a SUMIFS function instead given the values desired to calculate are numeric. Utilizing a SUMIFS function you can add the multiple desired criteria to pull the most recent date and sum all relative numeric values.
SUMIFS: https://help.smartsheet.com/function/sumifs
If you need to visually display the rows text values for a specific date and need it to update as we move forward in time, based on your description, it may be simpler to utilize a Report to achieve. This Help Center article outlines in further detail Reports: https://help.smartsheet.com/articles/522214-creating-reports
If you are unable to produce the desired result after performing the above or if I have misunderstood the request, I think it would be best for us to set aside some time to have a screen share session to try to resolve the issue. Please connect with us utilizing this Support Form: https://help.smartsheet.com/contact
Please provide the best number you can be reached at and a Date/Time that would work best for you?
Note: Ensure to include your desired time zone.
Have a wonderful day. Thank you for contacting Smartsheet Support.
Cheers,
Eric
Smartsheet Technical Support
-
Hi Eric,
Thank you for your response. I was eventually able to figure it out. I appreciate your time and effort into writing a response! It was helpful!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!