0

My data looks something like (assume begins in A1):

 Category,Label1,Label2 
 Dogs,blank,doe
 Dogs,zoo,blank 
 Cats,zoo,yup 
 Cats,doe,hgg 
 Cats,blank,doe 

What the result should look like:

 Dogs, 2
 Cats, 4

My actual data has 100s of rows and 5 label columns.

I'm looking for a solution that either uses sumproduct or an array formula and can handle blanks and arrays of different sizes. I would like to avoid VBA if possible.

I ended up with this formula but still not the right answer:

=SUM((($A$2:$A$6="Dogs")*($B$2:$C$6<>""))/(COUNTIF($B$2:$C$6,$B$2:$C$6&"")))

I've tried concatenating (A2:A6&B2:C6) in countif range parameter but countif didn't accept this. I've tried sumproduct but it didn't like that I was using arrays of different sizes (1 column vs 2 column) and much more. I've spent the better part of two days researching and trying to solve this.

Looking forward to your help and expertise.

  • have you considered creating a pivot table? – David Dai Oct 2 '15 at 1:20
  • Why is the Cats result 4? – Michael Frank Oct 2 '15 at 1:37
  • The ideal formula would involve concatenating the five columns together and the counting them for identical matches. An array formula can do this but to what cost on performance? Each cell has to concatenate itself then to get the result has to concatenate all the other rows. For a spreadsheet with only a hundred rows, the concatenation will happen 100x100 times – wbeard52 Oct 2 '15 at 1:50
  • My suggestion would be a helper column that concatenates all the columns into one. – wbeard52 Oct 2 '15 at 1:50
  • @DavidDai Today I can accomplish what I need by manually stacking all Label columns (+category repeats in separate column) then inserting a pivot table on that data set (Category and Label in rows) then copying and pasting that and inserting another pivot table on that to get unique labels by Category. I'm looking to make my process easier since it's something I do often. – daniellopez46 Oct 5 '15 at 16:08
1

I would use the Power Query Add-In for this. It has a Group By command which includes a Count Distinct Rows operation. It's documented here (although they haven't yet caught up with that operation):

https://support.office.com/en-us/article/Group-rows-in-a-table-Power-Query-e1b9e916-6fcc-40bf-a6e8-ef928240adf1?ui=en-US&rs=en-US&ad=US

To prepare your data in Power Query (before using the Group By command), I would select the Category column and use the Transform / Unpivot Columns / Unpivot Other Columns command. This will transform Label1 and Label2 columns into Attribute and Value columns. I would Remove the Attribute column - that leaves just the Value column containing doe, zoo etc.

The final step is the Group By which will correctly count the distinct/unique values e.g. Cats = 4

  • 1
    This is a great solution! I heard of Power Pivot (not a serious user, played w/ it a bit before) but not Power Query. At first I thought this was going to be a more sophisticated SQL editor then MS query which would have been great as well since I am well versed in SQL. But this is way better! – daniellopez46 Oct 5 '15 at 17:39
  • I have been playing with Power query a bit this morning to see if this will really suite my needs. So far it seems that it will. I am just trying to figure out if sharing a .xlsx with Power queries is as easy as sending the file. Obviously whomever I share with will have to have the Power Query add-in. – daniellopez46 Oct 5 '15 at 17:42
  • 1
    It was as easy as emailing the file to someone who already has Power Query add-in installed. BTW Power Query was very easy to install (only took a couple of minutes). +1 for introducing me to tool that not only solved this problem but will solve another similar issue. I see myself using this tool a lot in the near future. I would give you +2 if I could. Thanks again! – daniellopez46 Oct 5 '15 at 18:12
  • Yes it's great - I've been doing a lot of work with it lately and it seems very flexible and also reliable. I think it fills a (massive) gap between hacking data around manually and building and maintaining a fully coded solution. BTW from Excel 2016, Power Query is built in to the Data ribbon under "Get and Transform" section. – Mike Honey Oct 5 '15 at 23:05

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.