Counting in groups

Question

Consider the following table in Excel:

status   | order | attribute 
---------------------------
purchased| table | brown
purchased| table | yellow
purchased| table |
purchased| sofa  |
purchased| sofa  | green
purchased| sofa  | 
purchased| pillow| brown
purchased| pillow| yellow
purchased| pillow|
shipped  | lamp  |
shipped  | lamp  |
shipped  | lamp  |
shipped  | desk  | brown
shipped  | desk  | 
shipped  | desk  |

I am trying to count each order by the attributes, so I can get a summary of the following data:

Number of unique orders with no attributes
Number of unique orders where one order has the brown attribute but no other attributes
Number of unique orders where one order has the brown attribute and another has the yellow attribute

and so on. Ideally these numbers could also be split by status as well.

But based on the above table, the summary should look like this:

Number of unique orders with no attributes: 1 (lamp)
Number of unique orders where one order has the brown attribute but no other attributes: 1 (desk)
Number of unique orders where one order has the brown attribute and another has the yellow attribute: 2 (table and pillow)

I've been Googling and testing for hours but to no avail.

@SolarMike Count() and Countifs() won't do unique. This can be done with pivot tables and pivot of pivots, or with modern Excel data model and the Distinct aggregation. — teylyn, May 28 '18 at 08:34
It is possible to do it with Frequencies, but I wouldn't really recommend it unless the other methods aren't available to you. — Tom Sharpe, May 28 '18 at 13:57

score 0 · Answer 1 · answered May 28 '18 at 08:38

Add the data to the Excel data model and then build pivot tables with the "distinct count" aggregation. Play with the order of things to arrive at your desired results. You may need to build more than one pivot table to show all your desired outcomes.

Counting in groups

1 Answers1