Countif lookup by email/contact

C Anderson
edited 12/09/19 in Formulas and Functions

Hello - 

I have a sheet ("Sheet 1") where users enter various entries/plans, etc. via a form and to associate it with themselves, I have them use their email address which logs into a contact column.

In another sheet ("Sheet 2")- I have a list of everyone who is expected to put in information. I have a contact column with the email address, however Smartsheet recognizes their user info and creates a contact for them that shows the name, email associated, and icon.

On sheet two, in a column to count the # of entries by user, I've tried to use the formula =COUNTIF({Who}, Email@row) where {Who} looks at the column of email addresses inputted by the form on Sheet 1. Email@row looks at the contact column of Sheet 2 where I've inputted email addresses that auto recognize as user contacts.

The formula will not count the 'plain' email addresses from inputting on the form. If I go into sheet 1, and start typing the email address - Smartsheet will replace with the user contact info and the formula will count a value. It seems that if entered by form though - there is no way to have this be recognized.

Any thoughts on either having the email address associated with a contact by used as criteria - or having email addresses from forms becoming associated with known user contacts?

Thanks in advance.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!