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.
