Formula help

Vanessa Tanner

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

Frank Falco

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

Frank Falco

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

Vanessa Tanner

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.