Solved

# COUNTIF

Posted on 2012-03-19
262 Views
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...

0
Question by:Friggin_Lazy

LVL 45

Expert Comment

ID: 37740291
Your example seems faulty. There are 3 'Apple 1' not 2.
0

LVL 8

Expert Comment

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

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

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

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

## Featured Post

### 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…