Index/Match Help
I have trying to create a formula so when we purchase materials which will be logged on my order list sheet, for a job using the Job reference from my job planner sheet. It will calculate the running cost of materials and bring it back to the material value column on my Planner.
I have been trying the below formula but this only picks up 1 row for when the Job reference is used, for example, we could purchase materials multiple times for one job reference so i am trying to have it work so when it matches a job reference of a full column, it will pick up the value and calculate.
=INDEX({[Cross sheet reference Materials Value column}, MATCH([Job reference]@row, {Cross sheet reference Job column},0))
Answers
-
How would the formula work when the job reference column will show difference reference for different department/Jobs. So for our cleaning department, the reference will always start with CL, how do i make the formula only calculated the job reference relating to the row on my planner.
-
Try something like this...
=SUMIFS({Cross sheet reference Materials Value column}, {Cross sheet reference Job column}, [Job reference]@row)
-
Thank you so much, i have spent hours working this out lol. Just one more, would this work on a column with multi select drop down? We used codes for description of works and their can be multiple codes within one cell?
-
You would want to incorporate a CONTAINS function to be able to search each cell for the text string.
=SUMIFS({Cross sheet reference Materials Value column}, {Cross sheet reference Job column}, CONTAINS([Job reference]@row, @cell))
-
Thats great, thanks for your help.
-
Happy to help! 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 382 Global Discussions
- 212 Industry Talk
- 444 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 451 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 291 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!