Formula SUM(COLLECT reference multiple columns

I have a formula that is picking up the value by date within a sales pipeline matching to the client name in the reporting sheet Account Name column, as thus:

=SUMIFS({Sales Pipeline Value}, {Sales Pipeline Client}, [Account Name]@row, {Sales Pipeline Sales Stage}, OR(@cell = "0 - Tender", @cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Expected Delivery}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2022))

Now I want to remove the client name and reference the same value by project type but I am receiving an error #INCORRECT ARGUMENT SET - what am I missing?

=SUMIFS({Sales Pipeline Value}, {Sales Pipeline Project Type}, [Account Name]@row, {Sales Pipeline Sales Stage}, OR(@cell = "0 - Tender", @cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Expected Delivery}, AND(IFERROR(MONTH(@cell), 0) = 6, IFERROR(YEAR(@cell), 0) = 2022))

I have tried an alternative way of writing the formula but it still does not work:

=SUMIFS({Sales Pipeline Value}, {Sales Pipeline Project Type}, OR(@cell = "Small Project"), {Sales Pipeline Sales Stage}, OR(@cell = "0 - Tender", @cell = "1 - Prospect", @cell = "2 - Quote / Proposal", @cell = "3 - Pending Outcome"), {Sales Pipeline Expected Delivery}, AND(IFERROR(MONTH(@cell), 0) = 5, IFERROR(YEAR(@cell), 0) = 2022))

Find more posts tagged with

Accepted answers

Kelly Moore

ok. Try wrapping (in your reference sheet) the formula above in a VALUE function. Curious - why are you dividing your equations by one? I noticed it in your date equation also.

Also, to make your formulas more robust and efficient, unless you specifically need to call out a row number (like 1189 above), replace the number with @row. This says to use whatever row you're on.

=VALUE([Weighted Forecast]@row/1)

All comments

Kelly Moore

ok. Try wrapping (in your reference sheet) the formula above in a VALUE function. Curious - why are you dividing your equations by one? I noticed it in your date equation also.

Also, to make your formulas more robust and efficient, unless you specifically need to call out a row number (like 1189 above), replace the number with @row. This says to use whatever row you're on.

=VALUE([Weighted Forecast]@row/1)

Michelle Maas

1189 is because the row is above not in line with the formula. I copied the referenced data down to the same line. Have no idea why the director puts /1. Can I do DATE or VALUE formula for the Delivery Date column and then see if this works? What would that be.. =[Expected Close Date]@row + ([Contract Term]@row / 1)

Edit - It worked, I had the wrong month referenced for testing! Changing the referenced data to same row and the value column formula to include VALUE, corrected the issue. THANK YOU! 🙏

Kelly Moore

I believe you'll get an invalid operation error in the date column with Value(). you can try. Right now though, the problem is the [Value] column.

Try this in your [Value] column.

=Value([Weighted Forecast]1189 / 1)

I would also try removing the division-by-1

=[Weighted Forecast]1189 and/or =VALUE([Weighted Forecast]1189)

Michelle Maas

@Kelly Moore Thank you, all sorted now. See above comment. Really appreciate you sticking in there with me to troubleshoot this. Have a good rest of your day. 😀

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.