Formula SUM(COLLECT reference multiple columns

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! 🙏

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

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. 😀