Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

SUMIF and INDEX/MATCH in one formula

Hi,

I am having difficulty with creating a formula and hoping someone can help. I think the main issue is my inexperience with the INDEX/MATCH function.

Target sheet: "Copy of Site Lookup Table-Monthly Budgets_TEST

Source sheet: "Archive-PO Requisitions"

I need to sum the "Final Total Price" in the source sheet when the "Site" and "Need by Month Year" match the "Site" and "Need by Month Year" in the target sheet and put it in the column "Actual in Archive".

Thank you for any help you can provide.


Best Answer

Answers

  • ✭✭✭✭✭

    Amazing! That worked. I have never heard of the Collect function. I will definitely look into that more. Thank you so much!

  • Community Champion

    NP. It is one of the most powerful tools in smartsheet formulas, I'm glad it worked for you.

  • I am trying to do something similar and attempting to use this formula but to no avail. I do not not have a lot of experience in using formulas so most likely missing something so minor. Any Idea what could be missing?

    Any entries in Source Sheet that Match Target sheet in "Description" column, trying to sum the values in "Request" column of Source sheet into "Request" column of Target sheet.

    =SUM(COLLECT({ISP Order Sheet Range 1}:{ISP Order Sheet Range 1}, {ISP Order Sheet Range 2}:{ISP Order Sheet Range 2},@cell = Description@row}))

  • Community Champion

    With other sheet references you don't use a colon.

    =SUM(COLLECT({ISP Order Sheet Range 1}, {ISP Order Sheet Range 2},@cell = Description@row}))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions

  • I have a pretty basic sheet that I am using to develop a formula, and am encountering an issue I haven't seen before. The formula isn't including my second row for some reason. It isn't a huge issue f…
    User: "jjg279"
    Answered ✓
    9
    2
  • I'm sure that this is an easy fix but I've tried a bunch of different ways and can't get this to work. I need to get the max date from a sheet to feed it into my meta data sheet (dates are stored in d…
    User: "susanmgfin"
    Answered ✓
    8
    2
  • I need help to come up with the risk formula. I tried many different formulas and tried to modify it but just can't seem to have desired results. So these are the conditions I MUST meet: Program is a …
    User: "Ronak"
    Answered ✓
    29
    6