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 :)
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives