Cost formula with a multiple select drop down column.

AlexisJ · 10/10/22

Hello,

I am having trouble with a cost formula. I am trying to sum the total cost of a certain department. The department is in a multiple select drop down column. They formula will only sum the departments with 1 dept selected and will not pick up the cost if the column has multiple departments. See screenshot below.

Fire should show $20 but is only showing $10

I have tired the following formals, but they are all coming back incorrect.

=SUMIFS(Cost:Cost, Department:Department, "Fire")

=SUMIFS(Cost:Cost, Department:Department, AND(FIND("Fire")))

=SUMIFS(Cost:Cost, Department:Department, FIND("Fire", @cell), >0)

=SUMIFS(Cost:Cost, Department:Department, FIND("Fire", Department:Department,))

I am hoping you can help me find the correct formula to use.

Thanks!

-Alexis

Ryan Sides · 10/10/22

Hi Alexis!

Try using the HAS function within the SUMIF function...

Did that help?

Ryan Sides · 10/10/22

Hi Alexis!

Try using the HAS function within the SUMIF function...

Did that help?

AlexisJ · 10/11/22

That worked! Thank you so much.

Ryan Sides · 10/11/22

@AlexisJ happy to help! Have a good one.

AlexisJ · 10/11/22

@Ryan Sides would you happen to have a solution for a formula that needs to sum multiple departments from the multiple select drop down? If I total Animal Services and Fire, it shows $0 instead of $30.