• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

I need a formula to give me the count of items in one sheet that are referenced in another

On Sheet 2, I have a master list of codes in column A with a short description (colors for example) in column B.

On Sheet 1, I have a sample of the master list of codes in Column A.  

I would like to have a formula in cell M4 on Sheet 1 that tells me how many of the codes in Column A of Sheet 1 are associated with the color red on sheet 2.

Please see the attached file to better understand what I am looking for. I want to make this is as easy as I can for my client. I just want to enter a formula for them once on the one cell on sheet 1. They will update column A on sheet 1 each day and need an accurate count of red based on sheet 2.

Thanks!
Test1.xlsx
0
Dominator1025
Asked:
Dominator1025
  • 6
  • 2
  • 2
1 Solution
 
SteveCommented:
Erm, the count works out to be zero, yes?
0
 
Dominator1025Author Commented:
Barman,

Sorry....you are right.  I didn't give any red codes in my sample, doh!  Not a trick question. Let's say Black instead, the answer should be 3.
0
 
SteveCommented:
Attached is an example with just a helper column...

There may be a single formula  answer, will see.
Test1.xlsx
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dominator1025Author Commented:
Barman,

I appreciate the attempt. I'm good with putting together a vlookup and then the countif, but my end user will be updating those codes in column A of sheet 1 every day and I don't want them to have to keep updating any other formulas besides the one we put in column H.

Is there a way to incorporate your index/match/countif into a single-cell array as seen here? http://office.microsoft.com/en-us/excel-help/introducing-array-formulas-in-excel-HA001087290.aspx
0
 
[ fanpages ]IT Services ConsultantCommented:
Also see:

"I need a formula to give me the count of items in one sheet that are referenced in another"

[ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28133305.html ]
0
 
Dominator1025Author Commented:
That is my post from 3 days ago. I reposted the same question here as I was not getting any additional answers there.  There is nothing to see there.
0
 
Dominator1025Author Commented:
I have found something that works.  Thanks for your time guys.

=SUMPRODUCT(('Sheet2'!B3:B274=G5)*COUNTIF(A2:A35,'Sheet2'!A3:A274))
0
 
[ fanpages ]IT Services ConsultantCommented:
That is my post from 3 days ago. I reposted the same question here as I was not getting any additional answers there.  There is nothing to see there.

Yes, I know.

My point was that you requested the deletion of a question then created a new thread with the same subject matter.
0
 
Dominator1025Author Commented:
My intention was to get the question to the top of the experts pages since the first one had not been live for a few days. I know you can request attention, but in my experience, it is much faster to just repost. Sorry.
0
 
Dominator1025Author Commented:
The other answers were not acceptable as they did not complete my question within a single cell. After playing with and combining some formulas, I discovered my answer worked as requested.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 6
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now