multiple sheet reference

Hi all,
I'm a beginner so excuse if my description of problem isn't great.
I have 2 sheets in a workbook. How do I count in a cell on sheet 2 (call it cell 2A1) how many times a value from another cell on sheet 2 (2A2) appears in a range on sheet 1, say K1:K20. I've managed to do it as a literal value but I want to reference 2A2 in the formula as a variable.
Any help would be appreciated.
Thanks

Well you put me on the right track. I needed =COUNTIF(SHEET1!K1:K20,SHEET2!A2)
got to it from your advice.
Thank you

Hi Jason - Welcome to the forum.

I couldn't quite understand your question, especially the cell reference (2A1 & 2A2).

I have attached a spreadsheet... Hope that helps....



Please remember the following:

1. Show appreciation to those who have helped you by clicking Add Repution below their posts.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Thanks for that spreadsheet. That's what I had. I used 2a2 to mean sheet2 cell a2. Probably should have made that clear. Apologies if that's not correct way to refer to cell.
I have another one if you could help...
Say sheet1 has A1:A15(apples, oranges or bananas), B1:B15 (date of order), C1:C15 (quantity), D1:15, (country) E1:E15 (buyer)
On sheet2, sheet3, sheet4- cell H1 says apples, oranges and bananas, respectively.
There are only columns for date of order (A), Quantity (B) and Buyer (C)
Is it possible for sheets 2, 3, 4 to fill the required values automatically from data entered on sheet1?

Jason,

I would appreciate if you could attach an example spreadsheet to understand the scenario better....

Okay Adil. Thanks.
Example.xlsx

That was a very decent Pivot that you provided popipipo..... Jason I would also recommend using Pivots in such cases....


Please remember the following:

1. Show appreciation to those who have helped you by clicking Add Repution below their posts.
2. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

Thanks Popipipo and Adil.master I haven't used pivot tables before but will look into their use and see if suitable for my purpose. I was hoping to make sheet 2 a template that would draw required information from sheet 1 automatically. My head hurts now.
Thanks for the help.