asked on

COUNTIF

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.

Martin Liss

Your example seems faulty. There are 3 'Apple 1' not 2.

csoussan

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

ASKER CERTIFIED SOLUTION

dlmille

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

krishnakrkc

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

Friggin_Lazy

ASKER

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