**Question description:**

*This user has given permission to use the problem statement for this blog.*

i need a formula to count values that meet two conditions: one if a value is contained in a cell and another one is cells aren't empty

Solved by X. Q. in 20 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
15/03/2018 - 08:55

Hi, How can I help you?

User
15/03/2018 - 08:56

I'm trying to find the right formula for this problem: i need to count how many people in Europe we have an email address for

Excelchat Expert
15/03/2018 - 08:57

Sure, I can definitely help you with this.

User
15/03/2018 - 08:57

knowing i distinguished on my sheet between people in EU (Y) and non EU (N)

User
15/03/2018 - 08:57

as some email cells are blank

Excelchat Expert
15/03/2018 - 08:58

So, if I am getting your problem right. You want the count the people with 'Y' who also has a non-blank email address

Excelchat Expert
15/03/2018 - 08:58

Is that right?

User
15/03/2018 - 08:59

yes perfect

Excelchat Expert
15/03/2018 - 08:59

okay. You can use a 'Countif' formulae for this

Excelchat Expert
15/03/2018 - 09:00

Is there any document you want me to write this formulae on?

Excelchat Expert
15/03/2018 - 09:00

Or should i show you in the google sheet and you will apply in yours

User
15/03/2018 - 09:00

yes that sounds good

Excelchat Expert
15/03/2018 - 09:01

can you see the dummy data i created?

User
15/03/2018 - 09:02

yes

Excelchat Expert
15/03/2018 - 09:02

I will now create a length column to check the length of the email address column. This will let me know whether the column (i.e. email address) is blank or not

Excelchat Expert
15/03/2018 - 09:03

Did you see, the blank mail Ids are coming with 0 length??

User
15/03/2018 - 09:03

right

Excelchat Expert
15/03/2018 - 09:03

Now i will get the count you want

User
15/03/2018 - 09:03

ooh

Excelchat Expert
15/03/2018 - 09:04

can you see the formulae i used. First we need to give the criteria range

Excelchat Expert
15/03/2018 - 09:05

which is column B

Excelchat Expert
15/03/2018 - 09:05

after that, what criteria we want to use, which is N

Excelchat Expert
15/03/2018 - 09:05

sorry which is "Y"

Excelchat Expert
15/03/2018 - 09:05

This is the first criteria

Excelchat Expert
15/03/2018 - 09:06

second critera is to check whether the email address is blank or not

Excelchat Expert
15/03/2018 - 09:06

Am i making sense here?

User
15/03/2018 - 09:06

so you used a COUNTIFS formula right i can't see it in the cell

Excelchat Expert
15/03/2018 - 09:06

I pasted the formulae again

Excelchat Expert
15/03/2018 - 09:06

is it visible now

Excelchat Expert
15/03/2018 - 09:07

?

User
15/03/2018 - 09:08

yes that makes so much sense

User
15/03/2018 - 09:08

i didn't think of adding that extra column

User
15/03/2018 - 09:09

thank you so much for your help!

Excelchat Expert
15/03/2018 - 09:09

Awesome. I hope my info was helpful and was able to solve your problem

User
15/03/2018 - 09:09

definetely and saved me so much time

Excelchat Expert
15/03/2018 - 09:09

That sounds great

Excelchat Expert
15/03/2018 - 09:09

It was nice talking with you

Excelchat Expert
15/03/2018 - 09:09

Have a great day!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*