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
253 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

911 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

21 Experts available now in Live!

Get 1:1 Help Now