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

2024-03-15 15_15_47-Weekly Totals 2024.png

2024-03-15 15_19_23-Quotes and Sales Master Log 2024.png

Thank you!

Find more posts tagged with

Accepted answers

Nick055

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

All comments

Nick055

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!

santmitbhamrah

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.

2024-03-19 09_28_12-Weekly Totals 2024.png

2024-03-19 09_29_00-Quotes and Sales Master Log 2024.png

Nick055

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.

Nick055

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

santmitbhamrah

Yes! it worked. Thank you so much!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.