Formula help
I am trying to create a count or tally formula. When two criteria are met then I consider that full contact.
Criteria: When the date of contact is filled out and the comments from that contact are filled out.
I want to do this over and over again about 40 odd times.
So if date #1 is filled out and comment # 1 is filled out the count would show 1
If date #2 is filled out and comment #2 is filled out the count would show 2
If date #3 is filled out and comment #3 is NOT it would show 2 so on and so forth.
I can do a column 40 times and then sum at the end but is there a better way to count these up instead?
below is what I am playing with
=IF(AND([Date of contact]@row <> "", [1st comment]@row <> ""), 1, IF(AND([2nd date of contact]@row <> "", [2nd comment]@row <> ""), 2, 0))
Thanks,
Vanessa
Best Answer

Start by using ISDATE([Date of contact]@row) for each to check whether it is a date, rather than <>""
If the logic is for each date and comment combination that is complete then add one, an option could be to do this:
=IF(AND(ISDATE([Date 1@row),[Comment 1]@row<>""),1)+IF(AND(ISDATE([Date 2@row),[Comment 2]@row<>""),1)+IF(AND(ISDATE([Date 3@row),[Comment 3]@row<>""),1)+...+IF(AND(ISDATE([Date n@row),[Comment n]@row<>""),1)
This way it adds 1 for each complete combination. Would be a big formula for 40 instances but it is easy to replicate
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
Answers

Start by using ISDATE([Date of contact]@row) for each to check whether it is a date, rather than <>""
If the logic is for each date and comment combination that is complete then add one, an option could be to do this:
=IF(AND(ISDATE([Date 1@row),[Comment 1]@row<>""),1)+IF(AND(ISDATE([Date 2@row),[Comment 2]@row<>""),1)+IF(AND(ISDATE([Date 3@row),[Comment 3]@row<>""),1)+...+IF(AND(ISDATE([Date n@row),[Comment n]@row<>""),1)
This way it adds 1 for each complete combination. Would be a big formula for 40 instances but it is easy to replicate
✅Did my post help answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Thank you so much for your help, Frank. This works way better than the nested if statement and accomplishes what I was looking for perfectly.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.4K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!