Find conditional frequency of unique text entries in Excel?

Question

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.

Answer 1

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: