# Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

i need to count specific values in a column B as long as they also are equal to a specific value in column A without double counting.
Solved by E. Y. in 19 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 27/08/2018 - 09:27
Hello, Welcome to Got it Pro
User 27/08/2018 - 09:28
Hi
User 27/08/2018 - 09:28
Thanks
User 27/08/2018 - 09:28
so, is my question clear?
Excelchat Expert 27/08/2018 - 09:29
So you want to count the specific values in Column B if that is Equal to A
User 27/08/2018 - 09:29
yes only if its equal to a specific text in A
Excelchat Expert 27/08/2018 - 09:29
Can you see the sheet on your right?
User 27/08/2018 - 09:29
yes
Excelchat Expert 27/08/2018 - 09:30
Can you see the sample data?
User 27/08/2018 - 09:30
yes
Excelchat Expert 27/08/2018 - 09:30
in row 3 and row 5 values are equal in both columns
Excelchat Expert 27/08/2018 - 09:30
so final result should be 2 right?
Excelchat Expert 27/08/2018 - 09:31
Is this what you need?
User 27/08/2018 - 09:31
no
User 27/08/2018 - 09:32
i changed it a bit
Excelchat Expert 27/08/2018 - 09:32
Now no values are matching
Excelchat Expert 27/08/2018 - 09:32
in col A and col B
User 27/08/2018 - 09:32
yes they shouldnt match
Excelchat Expert 27/08/2018 - 09:32
what result do you want in this case?
Excelchat Expert 27/08/2018 - 09:33
Col A and Col B data is there, what result you want to see in this case?
User 27/08/2018 - 09:33
i want to see how many Chem (in row B) if A has Bio
User 27/08/2018 - 09:33
so final result should be 2
Excelchat Expert 27/08/2018 - 09:34
So the result will be 2 based on this condition
Excelchat Expert 27/08/2018 - 09:34
ok, got it. let me see
Excelchat Expert 27/08/2018 - 09:36
Can you see the result
User 27/08/2018 - 09:37
yes
Excelchat Expert 27/08/2018 - 09:37
You can change the criteria In E1 and E2 and result will change
Excelchat Expert 27/08/2018 - 09:37
User 27/08/2018 - 09:37
User 27/08/2018 - 09:38
it should have changed to 3
User 27/08/2018 - 09:38
the result
Excelchat Expert 27/08/2018 - 09:38
I locked the reference range
Excelchat Expert 27/08/2018 - 09:38
let me change
User 27/08/2018 - 09:38
can you unlock so i can test with more
Excelchat Expert 27/08/2018 - 09:38
User 27/08/2018 - 09:39
ok, so what is the formula?
User 27/08/2018 - 09:39
and where in the formula did you write the text?
Excelchat Expert 27/08/2018 - 09:39
giving it to you
Excelchat Expert 27/08/2018 - 09:39
=COUNTIFS(\$A\$2:\$A\$100,\$E\$1,\$B\$2:\$B\$100,\$E\$2)
Excelchat Expert 27/08/2018 - 09:39
This is the formula
User 27/08/2018 - 09:40
what is the dollar sign?
Excelchat Expert 27/08/2018 - 09:40
In E1 and E2 i have provided the values so that you can change your criteria
Excelchat Expert 27/08/2018 - 09:40
Right now it is looking for Bio in col A and Chem in Col B .. if you want to count some other combination you can change in E1 and E2
Excelchat Expert 27/08/2018 - 09:41
Dollar signs are use to lock the reference value so that when you copy and paste the formula to other cells, it wont get affected
User 27/08/2018 - 09:41
nope... i need to have everything in the same formula
User 27/08/2018 - 09:41
in 1 cell
Excelchat Expert 27/08/2018 - 09:41
ok
Excelchat Expert 27/08/2018 - 09:41
=COUNTIFS(\$A\$2:\$A\$100,"Bio",\$B\$2:\$B\$100,"Chem")
Excelchat Expert 27/08/2018 - 09:41
here you go
Excelchat Expert 27/08/2018 - 09:41
everything in the same formula
Excelchat Expert 27/08/2018 - 09:42
Did it solve your query my dear friend?
User 27/08/2018 - 09:42
i think so
User 27/08/2018 - 09:42
but what is the dollar sign?
Excelchat Expert 27/08/2018 - 09:43
just scroll a bit above. I already wrote about dollar sign to you.
User 27/08/2018 - 09:43
because it doesnt look like part of the formula
Excelchat Expert 27/08/2018 - 09:43
it is part of the formula onlt
Excelchat Expert 27/08/2018 - 09:43
only
User 27/08/2018 - 09:43
so on my excel i put the dollar sign?
Excelchat Expert 27/08/2018 - 09:44
Dollar signs are use to lock the reference value so that when you copy and paste the formula to other cells, it wont get affected
Excelchat Expert 27/08/2018 - 09:44
I will suggest, it will never hamper your range
Excelchat Expert 27/08/2018 - 09:44
if you copy the cell somewhere else in your excel.
User 27/08/2018 - 09:44
ok, well thanks a lot
Excelchat Expert 27/08/2018 - 09:44
Thanks for joining got it pro. You can end the session now, please provide the highest possible rating. Looking forward to help you further.
Excelchat Expert 27/08/2018 - 09:44
Have a gud day :)
User 27/08/2018 - 09:44
hi
Excelchat Expert 27/08/2018 - 09:45
Yeah
User 27/08/2018 - 09:45
would it be possible if you just hold on while i test?
User 27/08/2018 - 09:45
i would really appreciate it
Excelchat Expert 27/08/2018 - 09:45
Ok
Excelchat Expert 27/08/2018 - 09:45
Are you testing it at your excel ?
Excelchat Expert 27/08/2018 - 09:46
Please do it a little faster.
User 27/08/2018 - 09:46
ok
User 27/08/2018 - 09:47
it works thanks
Excelchat Expert 27/08/2018 - 09:47
:)

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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.