Countif lookup by email/contact

C Anderson

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.

Paul Newcome

There are a few different ways this could be set up.

 

The most effective way that I can think of right now is by building a directory of sorts where you store everyone's name and email address.

 

You can then use this to populate a column on sheet 1 to essentially automatically enter a person's name based on their email address.

 

Then on sheet two you would use the same formula, but you would look at this new column.

 

Does that sound like an option you would be interested in?

Andrée Starå

Hi,

I agree with Paul, and there are two options.

You could either use a VLOOKUP structure or a combination of INDEX/MATCH.

I prefer the INDEX/MATCH option, but it depends on the solution.

I hope that helps!

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Andrée Starå

More info: https://help.smartsheet.com/function/index

https://help.smartsheet.com/function/match