IF statements formula

Daniel Kumar

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

Jeff Reisman

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

Jeff Reisman

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