SUM Collect for Multi-selection Dropdown Criteria

Test Case 123

Hi,

I am looking to sum the total Services Revenue for all Opportunities with a Technology that includes "Custom." The Technology column dropdown is multi-select, so users can call out multiple technologies in addition to Custom as shown in the example. Other criteria such as Sales Stage will be included in the summation. I've been using the following formula in a metrics sheet but it only returns the values that only have Custom listed for technology and not multiple technology selections as shown in the example….HELP!

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, "Custom")

Screenshot Smartsheets.png

Michelle Choate 2

Hello @Test Case 123 ! This is an easy fix. Whenever you have a multi-select dropdown, you need to use a HAS() function. So your formula sound be:

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, HAS(@cell, "Custom"))

Let me know if this works! If it does, please mark my answer as the correct one 😊

Michelle Choate 2

@Test Case 123 - Do you want it to have both criteria or either criteria?

With Either it would be

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, OR(HAS(@cell, "Custom"),HAS(@cell, "Out of the Box"))))

Alternatively with both criteria just a small change to the above formula:

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, AND(HAS(@cell, "Custom"),HAS(@cell, "Out of the Box"))))

:) Have a great day!

Test Case 123

That worked! You are a lifesaver!!

Michelle Choate 2

Hello @Test Case 123 ! This is an easy fix. Whenever you have a multi-select dropdown, you need to use a HAS() function. So your formula sound be:

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, HAS(@cell, "Custom"))

Let me know if this works! If it does, please mark my answer as the correct one 😊

Test Case 123

Thanks @Michelle Choate 2 - that worked! Now what if I wanted to include both "Custom" and "Out of the Box" technologies. I've tried adding additional HAS statements but it doesnt return:

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, HAS(@cell, "Custom"), {Technology}, HAS(@cell, "Out of the Box")

Michelle Choate 2

@Test Case 123 - Do you want it to have both criteria or either criteria?

With Either it would be

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, OR(HAS(@cell, "Custom"),HAS(@cell, "Out of the Box"))))

Alternatively with both criteria just a small change to the above formula:

=SUM(COLLECT({Service Revenue}, {Sales Stage}, "6 - Won", {Technology}, AND(HAS(@cell, "Custom"),HAS(@cell, "Out of the Box"))))

:) Have a great day!

Test Case 123

That worked! You are a lifesaver!!