Please help with combined SUMIF, INDEX, and MATCH formula
I'm trying to sum the $ amount of quotes submitted every week of the year. I have my Quotes and Sales Master Log 2024 sheet which I'm referencing and Weekly Totals 2024 sheet to calculate data into. I have the #UNPARSEABLE error right now and I tried few variations of the same formula but haven't been able to figure it out yet, so if some can help that would be greatly appreciated.
This is my current formula: =SUMIF(INDEX({Quote Submitted Week No. from Q&S Master Log 2024}, , MATCH([Week Number]@row, {Quote Submitted Week No. from Q&S Master Log 2024}, )), [Week Number]:[Week Number], {$ Amount Quoted from Q&S Master Log 2024})
Thank you!
Best Answer

In words this formula does:
 COLLECT creates a range of items that match the criteria. In this case we need all the quote amounts on the other sheet IF the week number matches the week number of the row of the formula
 Then we're feeding that new range to SUM, giving you the total of all quotes with that week number.
The problem your formula runs into is INDEX returns one item. So if you feed that into a sum function, it'll just give the total of that single item.
SUMIFS should also work, but I am honestly not very experienced with that formula & SUM/COLLECT serves the same purpose.
I believe this is how you would set that up:
=SUMIF({Quote Submitted Week No from Q&S Master Log 2024}, [Week Number]@row, {$ Amount Quoted from Q&S Master Log 2024})
Answers

Hi,
I think we'll need to approach this another way.
Index / matching is returns a single value. So, using it in the SUMIF range field will only give you the sum of the first quote that matches the week.
I believe the best function for this is COLLECT.
=SUM(COLLECT({Quote Submitted Week No from Q&S Master Log 2024}, {$ Amount Quoted from Q&S Master Log 2024}, [Week Number]@row))
*make sure the arguments calling the other sheet are correct as I've typed them out
Let me know if that works!

Thank you @Nick055for help with this. I tried the formula you mentioned however it returned the unparseable error, I did some troubleshooting, but it then returned only zeros. Now I heavily modified the formula to
=IFERROR(SUM(INDEX(COLLECT({$ Amount Quoted from Q&S Master Log 2024}, {Quote Submitted Week No. from Q&S Master Log 2024}, [Week Number]@row), 1)), "")
which outputs some numbers but not the correct sum. I did a sheet summary which has the correct calculation, it should come out around 143,948.

Oh! I think I see what I did, I got the week# & quote amount references switched up.
Try this:
=SUM(COLLECT({$ Amount Quoted from Q&S Master Log 2024}, {Quote Submitted Week No from Q&S Master Log 2024}, [Week Number]@row))
The formula I originally sent was trying to match the Week No of the row with the quote amount, whoops.
Let me know if that works.

In words this formula does:
 COLLECT creates a range of items that match the criteria. In this case we need all the quote amounts on the other sheet IF the week number matches the week number of the row of the formula
 Then we're feeding that new range to SUM, giving you the total of all quotes with that week number.
The problem your formula runs into is INDEX returns one item. So if you feed that into a sum function, it'll just give the total of that single item.
SUMIFS should also work, but I am honestly not very experienced with that formula & SUM/COLLECT serves the same purpose.
I believe this is how you would set that up:
=SUMIF({Quote Submitted Week No from Q&S Master Log 2024}, [Week Number]@row, {$ Amount Quoted from Q&S Master Log 2024})

Yes! it worked. Thank you so much!
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 10.8K Get Help
 65 Global Discussions
 69 Industry Talk
 385 Announcements
 3.5K Ideas & Feature Requests
 56 Brandfolder
 125 Just for fun
 50 Community Job Board
 464 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!