**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.*