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
280 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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
 

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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

705 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