# If Formula Help

Hello all,

I created a requisition form with collumns to allocate products to 3 different locations and 7 departments inside of 1 of those 3 locations. Each location and department has a corresponding code. Our main location, "EHT" is 1-7 and the other two locations are 8 & 9.

The formula I'm using to allocate products by departments works because all of the codes are seperated until you get to 8 and 9.

=SUMIF(\$DC2:\$DC22, (@cell) >= 8, \$[Ext. Total]2:\$[Ext. Total]22)

My issue is when allocating by location, including departments 1-7 as one location.

=SUMIF(\$DC2:\$DC22, (@cell) > 1 < 8, \$[Ext. Total]2:\$[Ext. Total]22)

If I just do less than 8 then it includes items with no department code in EHT total sum. If an item has no code then I need it to be left out.

I hope this makes sense. Any and all help is greatly appreciated!

Fixed the problem! I hastily clicked no under "did this answer the question" when it totally did. @Mark Cronk thanks again!

• ✭✭✭✭✭✭

Try:

=SUMIFS([Ext. Total]2:[Ext. Total]22, DC2:DC22, AND(@cell > 1, @cell < 8, ISNUMBER(@cell)))

Mark

I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

• Hi Mark,

Sorry for the extremely delayed response!! The formula you provided returns a zero value. I messed around with it a little but to no avail.

• ✭✭✭✭✭✭

How are the numbers in the DC column being populated? @Mark Cronk's formula should be working for you.

• HEY! So the DC column was a multi-select dropdown and when I changed it to single select it worked perfectly fine! Thanks so much!

Fixed the problem! I hastily clicked no under "did this answer the question" when it totally did. @Mark Cronk thanks again!

• ✭✭✭✭✭✭

Glad you were able to get it working.

The column type made a difference because multi-selects output text strings only whereas single selects have the capability of outputting numerical values.

• Oh okay, that's great to know! Thanks again!

• ✭✭✭✭✭✭

Happy to help. 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!