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
-
=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.
Answers
-
=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.
-
Amazing! That worked. I have never heard of the Collect function. I will definitely look into that more. Thank you so much!
-
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}))
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!