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
DId it solve your query?
User
27/08/2018 - 09:37
i just added column 10
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
You can add now
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.
