SUMIF and INDEX/MATCH in one formula


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


  • alexis.ray89371
    alexis.ray89371 ✭✭✭✭✭

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

  • L_123
    L_123 ✭✭✭✭✭✭

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

  • L_123
    L_123 ✭✭✭✭✭✭

    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!