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
 10.5K Get Help
 62 Global Discussions
 46 Industry Talk
 386 Announcements
 3.5K Ideas & Feature Requests
 54 Brandfolder
 124 Just for fun
 50 Community Job Board
 466 Show & Tell
 40 Member Spotlight
 44 Power Your Process
 28 Sponsor X
 234 Events
 7.3K Forum Archives
Check out the Formula Handbook template!