IF statements formula
Hi,
I have made this formula to sum number of actions happening in each month:
Everything works fine when the dates is in the same year:
If for example the first date is in year 2021 and the rest is in 2022 I get a strange number back:
Formula for January:
=IF([Month 1]@row = 1; COUNTIF([Year 1]@row; 2022); "") + IF([Month 2]@row = 1; COUNTIF([Year 2]@row; 2022); "") + IF([Month 3]@row = 1; COUNTIF([Year 3]@row; 2022); "") + IF([Month 4]@row = 1; COUNTIF([Year 4]@row; 2022); "")
Formula for February:
=(IF([Year 1]@row = "2022"; COUNTIF([Month 1]@row; 2); "") + (IF([Year 2]@row = "2022"; COUNTIF([Month 2]@row; 2); "")) + (IF([Year 3]@row = "2022"; COUNTIF([Month 3]@row; 2); "")) + (IF([Year 4]@row = "2022"; COUNTIF([Month 4]@row; 2); "")))
Can anyone explain what I have done wrong here and how it can be solved in a good way?
Best,
Daniel
Best Answer
-
@Daniel Kumar What is happening is that you are specifying a blank TEXT value as your negative condition in your IFs, and then adding them together. Once Smartsheet finds text in a cell, everything after that is considered text. When you add text together or add text to number values, Smartsheet treats this as "concatenate all the values together," since you can't do math on text values.
For your rows with 2021, the first IF is using its negative condition of the blank text, so everything after that in the addition part of the formula (adding the results of the IFs together) is treated as text. So how to get around this?
If you do not specify a "blank" text value for the negative condition in your IFs (no negative condition, or negative condition of numeric 0,) then Smartsheet keeps treating every result as a number value, and adds them together using math instead of concatenating them.
Try this with 0 as the negative conditions, see if it solves your problem with the 2021 rows.
=IF([Month 1]@row = 1; COUNTIF([Year 1]@row; 2022); 0) + IF([Month 2]@row = 1; COUNTIF([Year 2]@row; 2022); 0) + IF([Month 3]@row = 1; COUNTIF([Year 3]@row; 2022); 0) + IF([Month 4]@row = 1; COUNTIF([Year 4]@row; 2022); 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@Daniel Kumar What is happening is that you are specifying a blank TEXT value as your negative condition in your IFs, and then adding them together. Once Smartsheet finds text in a cell, everything after that is considered text. When you add text together or add text to number values, Smartsheet treats this as "concatenate all the values together," since you can't do math on text values.
For your rows with 2021, the first IF is using its negative condition of the blank text, so everything after that in the addition part of the formula (adding the results of the IFs together) is treated as text. So how to get around this?
If you do not specify a "blank" text value for the negative condition in your IFs (no negative condition, or negative condition of numeric 0,) then Smartsheet keeps treating every result as a number value, and adds them together using math instead of concatenating them.
Try this with 0 as the negative conditions, see if it solves your problem with the 2021 rows.
=IF([Month 1]@row = 1; COUNTIF([Year 1]@row; 2022); 0) + IF([Month 2]@row = 1; COUNTIF([Year 2]@row; 2022); 0) + IF([Month 3]@row = 1; COUNTIF([Year 3]@row; 2022); 0) + IF([Month 4]@row = 1; COUNTIF([Year 4]@row; 2022); 0)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.6K Get Help
- 403 Global Discussions
- 215 Industry Talk
- 455 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 56 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!