Solved

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

Posted on 2013-05-23
10
241 Views
Last Modified: 2013-05-28
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
Comment
Question by:Dominator1025
  • 6
  • 2
  • 2
10 Comments
 
LVL 24

Expert Comment

by:Steve
ID: 39191924
Erm, the count works out to be zero, yes?
0
 

Author Comment

by:Dominator1025
ID: 39191947
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
 
LVL 24

Expert Comment

by:Steve
ID: 39191962
Attached is an example with just a helper column...

There may be a single formula  answer, will see.
Test1.xlsx
0
 

Author Comment

by:Dominator1025
ID: 39192098
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39192189
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:Dominator1025
ID: 39192207
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
 

Accepted Solution

by:
Dominator1025 earned 0 total points
ID: 39192264
I have found something that works.  Thanks for your time guys.

=SUMPRODUCT(('Sheet2'!B3:B274=G5)*COUNTIF(A2:A35,'Sheet2'!A3:A274))
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 39192472
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
 

Author Comment

by:Dominator1025
ID: 39192638
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
 

Author Closing Comment

by:Dominator1025
ID: 39200666
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now