Cost formula with a multiple select drop down column.

AlexisJ

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

Hi Alexis!

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

Did that help?

Ryan Sides

Hi Alexis!

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

Did that help?

AlexisJ

That worked! Thank you so much.

Ryan Sides

@AlexisJ happy to help! Have a good one.

AlexisJ

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

Ryan Sides

@AlexisJ Does this work?