Formula SUM(COLLECT reference multiple columns

Michelle Maas

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

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

Smartsheet is being super glitchy. I checked the references and it changed all the working client formulas to the same error #INCORRECT ARGUMENT SET. I saved the sheet and refreshed and it worked again. Now for the project type formula, it is working but I am getting a 0.00 value when there should be sums. I changed the formula to the below and it works only if I remove the expected delivery (date) part of the formula:

=SUM(COLLECT({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) = 7, IFERROR(YEAR(@cell), 0) = 2022)))

Kelly Moore

Hey @Michelle Maas

Check your sheet to see if any errors are appearing in your sheets. I have seen errors cause problems - even though you have IFERROR trying to mitigate issues. I would first start with the Delivery Date column.

Kelly

Michelle Maas

@Kelly Moore Hi Kelly, thanks for getting back to me. Please refer to the second comment as I am no longer getting an error, it is just showing as 0.00. If I remove the date part of the formula, it works but then it is not breaking down the data to each month, thus summing up the value overall on the reference sheet.

I think you may be referring to the IFERROR - how do I fix it?

Michelle Maas

I tried the formula below, changing the IFERROR section to the front of the formula, but still getting 0.00:

=IFERROR(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(MONTH(@cell) = 8, YEAR(@cell) = 2022)), 0)

The column Expected Delivery sometimes will be blank until a date is added. Do I use ISBLANK instead?

Kelly Moore

Hey

Sorry, I misinterpreted what you said. I thought it was still giving you errors. No, I wasn't referring directly to the IFERROR - if the cells within the referenced sheet have errors within the cells (Blocked, NoMatch, etc), I have seen these produce unexpected results or errors in formulas. I was suggesting to look at your sheets to check.

As a test, have you tried to break your date into the two components Month and Year so you can remove independently? This is only to help the troubleshooting of the formula. And, I hate to ask, for the row where you are testing your formula - there is data for the account name at that row? When you removed the date criteria, am I to understand that the formula did produce a value other than zero?

Kelly

Michelle Maas

@Kelly Moore Hi Kelly, Yes I've tried just the month but it brings up errors. The sheet it is referring to, for the Delivery Date, it is sometimes blank until a date is added. The - {Sales Pipeline Project Type}, [Account Name]@row - part of the formula is referencing a column with project type and the Account Name column has a list of the project types to match for the formula. I have also tried = "Project Type" instead. Could it be that the Delivery Date referenced column is not working because it contains a formula? It works well for the very first formula I mentioned above.

Michelle Maas

@Genevieve P. - wondering if you can help us with the above?

Kelly Moore

Hey Michelle

Can you share a screenshot of your data.

What is the formula for your Date column? Did you extract this from a textstring? You can do a quick test of your dates by temporarily commandeering a text/number column on the sheet with the date, or inserting a quick column, and inserting =MONTH([your date column]@row). Pick a row that has an entry in it. Does it give you the month? Another quick test is =IF(ISDATE([your date column]@row), "true", "false"). Either of these will inform you how smartsheet is interpreting your data.

Michelle Maas

@Kelly Moore Hi Kelly, the first screenshot is the metrics sheet. The second is the reference sheet. Formula for the Delivery Date is: = [Expected Close Date] + [Contract Term] / 1)

I don't know what a textstring is. I tried entering a date in the delivery date column instead of a formula, refreshed the sheet and nothing changed. If I switch the Expected Delivery column from date to text/number column on the reference sheet, it gives me an error: #INVALID COLUMN VALUE.

I tested the formula =IF(ISDATE({Sales Pipeline Expected Delivery}), "true", "false") - it brings up error #INCORRECT ARGUMENT SET. If the same date/formula column works for the client metrics, surely it would work for the project type. I cannot understand why it will not work. For reference, see the first two formulas above.

Note the formula is picking up date from Level 1 above, not the same row.

Not sure if I am on the right track here. I am testing with a helper column to take the Delivery Date from text to value: =DATE(VALUE(20 + RIGHT([Expected Delivery Date]@row, 2)), VALUE(LEFT([Expected Delivery Date]@row, 2)), VALUE(MID([Expected Delivery Date]@row, FIND("/", [Expected Delivery Date]@row) + 1, 2)))

It is giving me an error: #INVALID VALUE

Kelly Moore

Hey Michelle

Thanks for the screenshots - screenshots always helps the community answer questions.

(1) To clarify - from your screenshot I see your Value in your reference sheet are all zeroes. Are there higher values listed in your sheet so that the Sum should be above zero?

Your date formula cleared it up for me, but when I suggested the date test I meant to place the formula (temporarily) in your reference sheet, not try to do the test in your metric sheet. But it's ok, no need.

Although eventually you may decide to wrap your entire formula in an IFERROR, as you just tried above, when trying to test a formula, I suggest leaving it off. The IFERROR may mask problems. I only add a final IFERROR around the entire formula when I'm confident the formula is working as desired.

Let's go back to the very basics, which I should have suggested earlier.

(2) In your formula, delete the reference(s) to your cross-sheet date range. Then, using the formula wizard, re-insert it. Make sure the correct column is referenced as you re-insert. You can do this on all of your ranges if desired. I always do this when I'm stuck on a formula not working.

After verifying your references, I'll make one suggestion to your original formula

(3) =SUMIFS({Sales Pipeline Value}, {Sales Pipeline Expected Delivery}, ISDATE(@cell), {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))

Michelle Maas

@Kelly Moore Thanks for your patience in supporting me with this. There are figures in the children row that are not shown in the parent. The formula is designed to ignore the levels and refer to the columns. I can compare the project type metrics to the client to see if the formula is working correctly.

I have "rebooted" the cross-sheet data references numerous times to check if this was the issue in the first instance. I tried copy and paste your formula, rebooted references..... still getting 0 😐️

When I use the following formula without the date, I get values, so at least that part works, but I still need it by month: =SUM(COLLECT({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")))

Kelly Moore

Do you get a non-zero value/non-error for each?

(1) =SUM({Sales Pipeline Value})

(2) =SUMIFS({Sales Pipeline Value}, {Sales Pipeline Expected Delivery}, ISDATE(@cell))

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

If you want to share the sheet, we can do that

Michelle Maas

(1) =SUM - #INVALID OPERATION

(2) =SUMIFS - 0.00

(3) without date - 0.00

The sheet is commercially sensitive, so can't share. However, if you're up for it, I can do screenshare via Teams?

=SUMIFS({Sales Pipeline Value}, {Sales Pipeline Project Type}, [Account Code]@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) = 10, IFERROR(YEAR(@cell), 0) = 2022))

The above is working now. I changed the column date reference to another date. Deleted that temporary reference and then added the correct one again, and it worked BUT it is not recognising the project type. AAAH!!

Kelly Moore

There's a problem in your Value column if the Sum function didn't work. It should have returned the sum of that column.

=SUMIFS({Sales Pipeline Value},{Sales Pipeline Value},ISNUMBER(@cell))

Michelle Maas

Ah that is because the value column is also a formula - I didn't realise =[Weighted Forecast]1189 / 1

I tried the ISNUMBER.. works without the additional bracket at end, but still get 0. Tried swapping out the "Small Project" for [Account Name]@row, didn't work.