SumIfs with absolute numbers

Hello Smartsheet Community,

I am trying to sum up the numbers in one column and I am running into some issues. Here is the situation and my challenge.

  • the column "UNITS" (drawn from a different sheet) has positive and negative numbers (inventory addition and deletions) and I need the total amount of how much inventory has been added and deleted, NOT the net amount. so if I use SUM the negative numbers get subtracted from the total amount which is NOT what I need
  • I tried =SUMIF({Units}, >0) - SUMIF({Units}, <0) which works well

My issue starts when I am trying to add conditions to that function. I only want to add the UNITS if the column "YEAR" in that external sheet says 2021. As the original data in the sheet says Q1 2021, Q4 2021 etc, I believe I have to go with FIND. here is what I came up with

=SUMIFS({Units}, >0, {year}, FIND("2021", @cell) > 0) - SUMIFS({Units}, <0, {year}, FIND("2021", @cell) > 0)

it looks great but it doesn't work :p

any idea how i can sum up absolute numbers with additional conditions?

Thanks in advance!


Best Answer

  • Leibel S
    Leibel S ✭✭✭✭✭✭
    Answer ✓

    @Antje B.

    Slight modification to your formula:

    =SUMIFS({Units}, {Units}, >0, {year}, FIND("2021", @cell) > 0) - SUMIFS({Units}, {Units}, <0, {year}, FIND("2021", @cell) > 0)

    When using SUMIFS the first criteria range needs to be specified separately.