0

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.

4
  • 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.
    – DrZoo
    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
    – Electrino
    Commented Jun 18, 2018 at 21:23
  • 1
    @DrZoo thanks, I deleted the previous comment and added the @<username>
    – Electrino
    Commented Jun 18, 2018 at 21:25

1 Answer 1

0

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”:
                “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:

results

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .