# Find conditional frequency of unique text entries in Excel?

So I've had a look around the Internet and I'm running into a brick wall with everything I've tried.

I have an Excel worksheet with two columns of data; 'Country' and 'Shape'; below is a small sub-sample of the data:

```Country:   Shape:
us        cylinder
us        light
gb        circle
ca        circle
us        light
us        sphere
gb        circle
us        disk
us        disk
us        disk
us        circle
gb        fireball
us        disk
gb        unknown
us        oval
us        circle
ca        disk
us        disk```

What I'm trying to do is count the instances of a unique shape for a given country... e.g., if (us) then sum instances of (circle).

I've tried using a `SUM(FREQUENCY(IF()))` formula as inspired by Count unique text values with criteria (at Exceljet), but I just can't get it to work. Any suggestions?

The desired output would (after running it for multiple shapes) look something like this:

``````Country:    Circle:    Cylinder:    light:   etc...
us          2          1            2
gb          2          0            0
ca          1          0            0
``````

OR

even more simply,

``````us    circle    2
``````

and then I can, manually, input each value I get myself.

• Thanks for showing example input. Now, please show the desired output for the input, and show exactly what you tried and what results you got. Commented Jun 18, 2018 at 20:59
• Hi Electrino. Since you're new here, here is a small tip. When replying to a comment use the @<username> so the other person gets a notification. If you don't @ them, they won't get a notification. I'm not an excel expert, so @Scott will hopefully be able to help. Commented Jun 18, 2018 at 21:21
• @Scott I've followed a bunch of different examples on the internet, for example, the one found here: exceljet.net/formula/count-unique-text-values-with-criteria but I can't figure out how to adapt it... so, I'm not really getting any results Commented Jun 18, 2018 at 21:23
• @DrZoo thanks, I deleted the previous comment and added the @<username> Commented Jun 18, 2018 at 21:25

There are better ways to do this than `FREQUENCY`.

I’ll assume that you would prefer to create the grid that you showed as your first desired output.  I’ll also assume that you want to put it on the same sheet as your raw data (specifically, I’ll assume that you want to put it to the right of the input data, starting in cell `F1`).  If you want to put it on another sheet, the basic steps are the same, but you’ll have to use `Sheet1!A2` instead of plain `A2`, etc.

To create the row labels:

• Click on an empty cell that’s not adjacent to any cell that has data in it.
• In the “Data” tab, “Sort & Filter” panel, click on “Advanced”.  Fill in the resulting dialog box as follows:

• Select “Copy to another location”.
• Set the “List range” to Column `A`, or specify the range where the countries are (`A1:A19`).  You must have a heading (in cell `A1`) and you must include it in the range.
• Set the “Copy to” range to Column `F`.
• Select “Unique records only”.

Click “OK”.

Creating the column headers is similar:

• Click on an empty cell; click on “Advanced Filter”.  Fill in the resulting dialog box as follows:

• Select “Copy to another location”.
• Set the “List range” to Column `B` (or specify the range where the shapes are: `B1:B19`).
• Set the “Copy to” range to Column `G`.
• Select “Unique records only”.

Click “OK”.

• Select the deduped shape list in cells `G2:G9` (not including `G1`).
• Copy the cells by typing Ctrl+C or clicking “Home” → “Clipboard” → “Copy”.
• Click in cell `G1`.
• Click “Home” → “Clipboard” → “Paste” → “Transpose”:

• Select cells `G2:G9` again and clear them (e.g., press Del).

If you want the labels and headers sorted, do that.

Now, click in cell `G2` and type

``````=COUNTIFS(\$A\$2:\$A\$19, \$F2, \$B\$2:\$B\$19, G\$1)
``````

Drag/fill to the right and down appropriately.  For your sample data, that’s cells `G2:N4`. Here’s what I got:

• Thank you so much. Your answer was very clear and helped tremendously Commented Jun 19, 2018 at 0:25