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!
Antje
Best Answer

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

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.

Thanks Leibel, worked like a charm :)