SUMIF and INDEX/MATCH in one formula

alexis.ray89371

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.

Archive-PO Requisitions.xlsx

Copy of Site Lookup Table-Monthly Budgets_TEST.xlsx

L_123

=sum(collect({final total price},{site},@cell = site@row, {need by month year},[need by month year]@row))

You will have to make your other sheet references equal to the range you want to analyze.

L_123

=sum(collect({final total price},{site},@cell = site@row, {need by month year},[need by month year]@row))

You will have to make your other sheet references equal to the range you want to analyze.

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

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

m_ragland

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

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