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.

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

  • Thank you so much. Your answer was very clear and helped tremendously – Electrino Jun 19 '18 at 0:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.