Count cells containing unique text in cell A if cell B equals i.e. 1

**killi3000** · 03-11-2014, 06:56 AM

Hi
I have a spreadsheet like the table in the picture. I would like to count unique values in cell B if cell A equals a certain value.

This is the unsorted data
Skjermbilde.PNG

This is how I want to display the data
Skjermbilde2.PNG

Can anyone help me with this?

**XOR LX** · 03-11-2014, 06:58 AM

Hi and welcome to the forum!

Kindly upload an actual spreadsheet, not just a picture.

Click on Go Advanced and scroll down until you see Manage Attachments.

Obviously replace any confidential/sensitive information with dummy data if necessary.

Regards

**killi3000** · 03-11-2014, 07:02 AM

Dummydata.xlsx

Thank you. Like this?

**AZ-XL** · 03-11-2014, 07:03 AM

Hi
I used helper column for that. But I am sure here will be smarter answers that do not use helper columns. Here is my answer with related file

**XOR LX** · 03-11-2014, 07:07 AM

Or, in E2 and copy across:

=SMALL(IF(FREQUENCY($A$3:$A$15,$A$3:$A$15),$A$3:$A$15),COLUMNS($A:A))

In E3 and copy across, array formula**:

=SUM(IF(FREQUENCY(IF($A$3:$A$15=E2,MATCH($B$3:$B$15,$B$3:$B$15,0)),ROW($B$3:$B$15)-MIN(ROW($B$3:$B$15))+1),1))

Regards

**killi3000** · 03-11-2014, 08:05 AM

Thanks for Your help! This worked wonderfully!

**killi3000** · 03-14-2014, 09:05 AM

This worked fine With text, but it doesn't seem to work as well With numbers?
=SUM(IF(FREQUENCY(IF($A$3:$A$15=E2,MATCH($B$3:$B$15,$B$3:$B$15,0)),ROW($B$3:$B$15)-MIN(ROW($B$3:$B$15))+1),1))

Is there another formula that Works better With numbers?

**XOR LX** · 03-14-2014, 09:28 AM

Can you post an example of what you mean?

Regards

**killi3000** · 03-14-2014, 09:32 AM

Sorry!
My bad. The formula is correct. I just thought I had another set of data

Count cells containing unique text in cell A if cell B equals i.e. 1

LinkBack

Thread Tools

Rate This Thread

Display

Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Re: Count cells containing unique text in cell A if cell B equals i.e. 1

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] When one cell equals value of another cell, then third cell displays text of fourth cell?

return unique list if cell value equals a1

If text in cell equals text in another cell, highlight both same color.

If Cell equals text Copy Cells, B2,B3,B4

Genarating count of unique words in a cell or cells

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions