[Solved] Count unique values in a list with 2nd criteria

[Incompetente]

Hi there.
I m searching a function outputting the number of unique values in a list with a second (or more) criteria.

An example:

1 R
1 R
2 R
3 R
4 D
4 D
5 R
5 R
5 R
6 L
7 D

I have two columns. One with numbers and the other one with different labels (R,D,L)
I would do like to count the number of "R" (or D or L) with unique values of the first column. In this case shoud be R=4, D=2, L=1

I don't know how to explain better the result I desire. Is it clear? Any suggestion?

[FJCC]

The only way I can think of is to use a Pivot Table and then a row to count the values greater than zero in the table. In the attached file, cells E14:G14 have the results you want.

[Lupp]

Or the old trick again:

Code: Select all

=SUMPRODUCT($A$1:$A$30 & $B$1:$B$30<>"";1/COUNTIF($A$1:$A$30 & $B$1:$B$30;$A$1:$A$30 & $B$1:$B$30))

for the first 30 rows.

You may prefer

Code: Select all

=SUMPRODUCT($A$2:$A$451&$B$2:$B$451<>"";MATCH($A$2:$A$451&$B$2:$B$451;$A$2:$A$451&$B$2:$B$451;0)=(ROW($A$2:$A$451)-ROW($A$2)+1))

if you aren't fond of the idea to get a number (tally) assured to be integer as a sum of fractions. (Now for a displaced range to explain for what the complication in the ROW() part was introduced.

[Incompetente]

The only way I can think of is to use a Pivot Table and then a row to count the values greater than zero in the table. In the attached file, cells E14:G14 have the results you want.

It seems to work. Could you explain me how to create this Pivot Table? How do you obtain the amount of letters for each number? And If I change my data, will this table update?

Or the old trick again:

Code: Select all

=SUMPRODUCT($A$1:$A$30 & $B$1:$B$30<>"";1/COUNTIF($A$1:$A$30 & $B$1:$B$30;$A$1:$A$30 & $B$1:$B$30))

for the first 30 rows.

You may prefer

Code: Select all

=SUMPRODUCT($A$2:$A$451&$B$2:$B$451<>"";MATCH($A$2:$A$451&$B$2:$B$451;$A$2:$A$451&$B$2:$B$451;0)=(ROW($A$2:$A$451)-ROW($A$2)+1))

if you aren't fond of the idea to get a number (tally) assured to be integer as a sum of fractions. (Now for a displaced range to explain for what the complication in the ROW() part was introduced.

Sorry, this function is too complex for my brain and I don't know how to make it work . Can you explain me a bit the logic of it?

[Lupp]

Most basic
There are two different approaches implemented by my formulas. They have in common that they need to be evaluated under "force array" condition.

"Force array"
This means that the used arrays will not return one result as a single value, but an array of results calculated for every element-index separately. Generally you need to enter a formula with Ctrl+Shift+Enter to get this mode of evaluation, but SUMPRODUCT() is one of the functions for which the parameters are specified "force array" anyway.

SUMPRODUCT()
is a badly named function. It doesn't calculate a product of sums, but a sum of products. To be able to do so, the function needs equally dimensioned arrays as its parameters, (There are more details, of course.)

Using compounds exceptionally
Generally compound data should be deprecated. In special cases they are useful. To compare for all the column contents contributing to the data for one index separately would balloon the formulas even more. Therefore, the partial contents are concatenated in advance of comparing the rows.

Second suggestion
The second solution needs a rather long (many characters) formula, but is logically simple: The first parameter returns a logical TRUE equivalent to a numeric 1 if the respective element not is the empty string. Abandoning all the other parameters we would simply count the non-empty elements (rows). The additional parameter, however contributes a factor being a logical FALSE equivalent to a numeric 0 if the respective element not is the first occurrence. The found, match points to a different row then. A factor 0 annullates the 1 from the first parameter, and SUMPRODUCT will therefore count nothing for second or higher occurrences of the same compound.

First suggestion
Here are all the non-empty rows "counted" - without exception but with a weight often less than 1 in many cases. What each occurrence contributes isn't simply 1 (one), but the reciprocal (1/n) of the multiplicity (n) with whitch the value is occurring. Thus a value occurring n times is counted n times with a weight of (1/n) which makes 1 at the end.
This trick I first found presented in this forum years ago. I suppose it was posted by Mrprogrammer then.

[FJCC]

Could you explain me how to create this Pivot Table? How do you obtain the amount of letters for each number? And If I change my data, will this table update?

To see how I laid out the Pivot Table, right click on it and select Edit Layout. In the Fields area on the right, you will see tiles labeled Number and Letter. These represent the two columns in the data. In the Layout section on the left, you will see that a Number tile is in the Row Fields area, a Letter tile in in Column Fields and a tile labeled Count - Letter is in the Data Fields. If you Double click on Count - Letter, you will see that there are several functions that can be applied to the Letter column and I chose Count. At the lower right of the dialog there is a button labeled More. If you click that, you will see that I chose to put the Pivot Table in cell Sheet1.D2.

To make the table from scratch, highlight all of the data cells, including the headers, and then choose the menu item Data -> Pivot Table -> Create. Choose to make it from the Current Selection. You will then see the dialog I discussed in the previous paragraph except that the Layout area will not have any tiles in it. Drag tiles from the Fields area to the Layout as needed. When you drag Letter to the Data Fields, the default function is Sum. Double click on the tile and change that to Count. Don't forget to click More and set the location of the table. The default choice for this is almost never a good one.

If you add data, the table will not automatically update but the process is simple. Select as many rows as needed starting just under the column headers Number and Letter, selecting cells in both column A and B. For example, if you need to add three data points, select the cells A2:B4. Choose the menu Insert -> Cells then choose the option to Shift Cells Down. Enter the data in the new cells then right click on the Pivot Table and choose Refresh.

[MrProgrammer]

Hi, and welcome to the forum.

I m searching a function …

People frequently make tasks harder by trying to do everything with functions instead of using features like Data Pilot, filtering, Text to Columns, Multiple Operations, Subtotals, etc. Calc is more than just functions.

I would do like to count the number of "R" (or D or L) with unique values of the first column. In this case shoud be R=4, D=2, L=1

I've added a column First which uses a SUMPRODUCT formula to locate the first occurrence of each Number:Letter combination. Then a pivot table with Letter in Row Fields and First in Data Fields immediately provides the requested information.

202102030916.ods

Spreadsheet

(12.98 KiB) Downloaded 131 times

Pivot table

And If I change my data, will this table update?

No, but often all one needs to do is right-click the pivot table and select Refresh. FJCC has more complete instructions. Of course, you should read the documentation if you want to better understand pivot tables. Using the DataPilot Pivot tables

… count the values greater than zero in the table …

I think what I've done with my First column is equivalent to your approach, but I do the calculation before creating the pivot table and you do yours afterward.

SUMPRODUCT() is a badly named function.

I too wish it were named differently. The function is so useful that it should have a shorter name, say SP(). The tutorial below shows over two dozen ways it can be used.

This trick I first found presented in this forum years ago. I suppose it was posted by MrProgrammer then.

I learned the technique from others, perhaps 25 years ago. I use it sometimes, though I prefer FREQUENCY now.

[Tutorial] The SUMPRODUCT function

If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the subject field. Select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

[Incompetente]

Most basic
There are two different approaches implemented by my formulas...

Thx a lot for the long explanation. I think this is too advanced for me. I will study better array and the function SUMPRODUCT() and I will return to try this.

Hi, and welcome to the forum.

Thx

People frequently make tasks harder by trying to do everything with functions instead of using features like Data Pilot, filtering, Text to Columns, Multiple Operations, Subtotals, etc. Calc is more than just functions.

You're right. I'm a kind of beginner in this world so I often use the wrong tool for what I need.

I've added a column First which uses a SUMPRODUCT formula to locate the first occurrence of each Number:Letter combination. Then a pivot table with Letter in Row Fields and First in Data Fields immediately provides the requested information.

Thx u. Your solution work pretty well too.

Of course, you should read the documentation if you want to better understand pivot tables.

And thx for all the tutorial and guide you linked. I need to study more to understand at least the basic of this software

To see how I laid out the Pivot Table, right click on it and select Edit Layout. In the Fields area on the right, you will see tiles labeled Number and Letter...

Thx you very much. Now it is super clear.