Index/Match Help

Richard Grimes

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

Paul Newcome

You would want to look into either a SUMIFS or a COUNTIFS depending on your exact needs (links included).

Richard Grimes

Capture3.PNG

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.

Paul Newcome

Try something like this...

=SUMIFS({Cross sheet reference Materials Value column}, {Cross sheet reference Job column}, [Job reference]@row)

Richard Grimes

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?

Paul Newcome

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

Richard Grimes

Thats great, thanks for your help.

Paul Newcome

Happy to help! 👍️