Question description:
This user has given permission to use the problem statement for this
blog.
i need a formula to find values in column f in worksheet 1 and count them.
Solved by B. A. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
06/03/2018 - 11:59
Hello, I understand that you need help with counting values in your worksheet. I can also see that you are creating a data sample in the document preview to the right.
User
06/03/2018 - 11:59
yes
Excelchat Expert
06/03/2018 - 11:59
So you want to know how many caf, dnd, aafc and swc are there, right?
User
07/03/2018 - 12:00
i have data in a table in sheet 1 and an organised list in sheet 2
Excelchat Expert
07/03/2018 - 12:00
I see. Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question.
User
07/03/2018 - 12:00
i want to somehow put a count next to each value in sheet two with the corresponding number in sheet 1
Excelchat Expert
07/03/2018 - 12:00
Using the sample you've provided, what we can use is the formula called COUNTIFS().
Excelchat Expert
07/03/2018 - 12:00
I'll show you then explain how it works.
User
07/03/2018 - 12:00
to clarify i know how to count them
Excelchat Expert
07/03/2018 - 12:01
I'm not sure I understood what you meant by your last message.
Excelchat Expert
07/03/2018 - 12:01
So you do know how to use countifs() already?
User
07/03/2018 - 12:02
i know how to use countif in the same sheet, i want to know how to make a count if for my list in sheet 2, using the data in sheet 1
User
07/03/2018 - 12:02
yes i do
Excelchat Expert
07/03/2018 - 12:02
It will still be countifs(), you'll just have to reference the sheet properly.
User
07/03/2018 - 12:02
sorry i just want to make sure i use the question wisely haha
Excelchat Expert
07/03/2018 - 12:03
Please see the formula in sheet2 column B.
User
07/03/2018 - 12:03
okay can you show me how to reference the sheet please
Excelchat Expert
07/03/2018 - 12:03
You'll see that instead of just referencing the range, it references the sheet in the actual formula as well.
Excelchat Expert
07/03/2018 - 12:03
You can write Sheet1!A:A which will mean that it is referencing column A of Sheet1
Excelchat Expert
07/03/2018 - 12:04
You can also simply select the cells you want to reference and Excel will automatically write the proper sheet and cell reference.
Excelchat Expert
07/03/2018 - 12:04
For example, type = in any cell in Sheet2
Excelchat Expert
07/03/2018 - 12:04
Then without pressing Enter, click Sheet1 and then select a cell then press Enter.
Excelchat Expert
07/03/2018 - 12:05
It will automatically create the cell reference for you and it will include the sheet name.
User
07/03/2018 - 12:05
one sec let em test it
Excelchat Expert
07/03/2018 - 12:05
Sure.
Excelchat Expert
07/03/2018 - 12:08
You were referencing E1, you need to reference D1
Excelchat Expert
07/03/2018 - 12:08
There you go.
Excelchat Expert
07/03/2018 - 12:09
For D1, you can just click D1 as well instead of manually typing it.
Excelchat Expert
07/03/2018 - 12:09
That way, you can be sure that you are referencing the right cell.
Excelchat Expert
07/03/2018 - 12:09
Here's the exact way to do it for your reference:
Excelchat Expert
07/03/2018 - 12:09
Type =countifs(
then click Sheet1 at the bottom
Using your mouse, highlight column A.
Click back to Sheet2 at the bottom of your sheet
then click D1 and press Enter.
User
07/03/2018 - 12:10
okay i got it
User
07/03/2018 - 12:11
can i link this to a table
Excelchat Expert
07/03/2018 - 12:11
Thats great!
User
07/03/2018 - 12:11
like if my list in sheet 2 was a table
User
07/03/2018 - 12:11
would this still work
Excelchat Expert
07/03/2018 - 12:11
Yes just follow the same steps.
User
07/03/2018 - 12:11
okay great thanks alot!
Excelchat Expert
07/03/2018 - 12:12
Sometimes it will automatically change the range to the assigned column name of the table but it should still work.
Excelchat Expert
07/03/2018 - 12:12
You are welcome.
Excelchat Expert
07/03/2018 - 12:12
Anything else regarding the original question?
User
07/03/2018 - 12:12
okay sweet thanks
User
07/03/2018 - 12:12
thats all for now!
User
07/03/2018 - 12:12
thanks again
Excelchat Expert
07/03/2018 - 12:13
I'd appreciate a 5 star rating after ending the session.
Excelchat Expert
07/03/2018 - 12:13
Thank you for contacting GotIt Pro! Have an awesome day!
User
07/03/2018 - 12:14
you got it! & you too :)
Excelchat Expert
07/03/2018 - 12:14
Thanks. That will help me a lot! Bye for now.
Excelchat Expert
07/03/2018 - 12:15
Please end the session by clicking the end session button.
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.
