If Formula Help

Jeffrey Cross

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!

Jeffrey Cross

https://community.smartsheet.com/discussion/comment/292858#Comment_292858

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

Mark Cronk

Try:

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

Mark

Jeffrey Cross

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.

Paul Newcome

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

Jeffrey Cross

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!

Jeffrey Cross

https://community.smartsheet.com/discussion/comment/292858#Comment_292858

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

Paul Newcome

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.

Jeffrey Cross

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

Paul Newcome

Happy to help. 👍️