Solved

COUNTIF

Posted on 2012-03-19
5
262 Views
Last Modified: 2012-03-20
Hello Everyone

I have a sheet with data.. example

Column 1    Column 2
Apple 1          Pear 1
Apple 2          Pear 2
Apple 1          Pear 1
Apple 3          Pear 6
Apple 1          Pear 7

Another Sheet With:

Column 1      Column 2
Apple 1              2
Apple 2               1
Apple 3              1
Apple 4              1

Is basically looking at column 2 of sheet 1 to determine how many unique entries
belong to Apple 1 etc...

I hope I made sense.
0
Comment
Question by:Friggin_Lazy
5 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37740291
Your example seems faulty. There are 3 'Apple 1' not 2.
0
 
LVL 8

Expert Comment

by:csoussan
ID: 37740351
As Martin stated above, the count for Apple 1 seems incorrect in your example as there are three listed.  However, assuming that the number 2 is just a typo, I think the following formula will do what you need.

=COUNTIF(Sheet1!A2:B6,A2)

I've attached a spreadsheet with your first set of columns in Sheet1 and the second set of columns (with formulas) on Sheet2.  Note - You can use COUNTIFS in place of COUNTIF and still get the same results.  The upside to COUNTIFS is you can list multiple criteria.

Hope this helps!
COUNTIF.xlsx
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
ID: 37740393
Yes, there are indeed 2 distinct Apple 1's (you have to look at column B as well, correct?)

Put this formula in Sheet 2, cell B2

[B2]=SUMPRODUCT((Sheet1!$A$2:$A$6=$A2)*IF(COUNTIF(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6)=0,"",1/COUNTIF(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6)))

Hit CTRL-SHIFT+Enter to confirm as an array formula.  Then, copy down as needed.  Note the bounds of the formula are to row 6, so you can extend that as needed.

See attached and I get the same results you do, except there is no Apple 4 in the first sheet, lol ;)

Dave
countApples-r1.xls
0
 
LVL 18

Expert Comment

by:krishnakrkc
ID: 37740547
Hi

Another option..

=COUNT(IF(FREQUENCY(IF(Sheet1!$A$2:$A$6=A2,MATCH(Sheet1!$B$2:$B$6,Sheet1!$B$2:$B$6,0)),ROW(Sheet1!$A$2:$A$6)-ROW(Sheet1!$A$2)+1),1))

Array formula. Confirmed with CTRL + SHIFT + ENTER

Kris
0
 

Author Closing Comment

by:Friggin_Lazy
ID: 37741098
Thank You Dave!  This is exactly what I was looking for.  I also appricate the attachment as an example!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

708 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