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
261 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for…
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

777 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