# 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

Tags:

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

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