SumIfs with absolute numbers

Antje B.

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

Leibel S

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

Leibel S

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

Antje B.

https://community.smartsheet.com/discussion/comment/290469#Comment_290469

Thanks Leibel, worked like a charm :)