1

enter image description here

Hi

I have an inventory list with a lot of products/IDS etc

I want to find out how many products is in one group only. In the picture above there will be products 455 and 700 - those are the only one that have one group only - the other products have multiple groups.

I am new to excel formulas and have tried countif and conditional formatting but I dont get the desired result.

Any suggestions?

I am using Excel 2016

/Henrik

Henrik Rosqvist
  • 65
  • 1
  • 1
  • 7

2 Answers2

1

With Excel 2021 and above use UNIQUE with condition "only once":

=UNIQUE(A2:A9,,TRUE)

For count, wrap it in COUNTA:

=COUNTA(UNIQUE(A2:A9,,TRUE))

Result:

enter image description here

user11222393
  • 3,245
  • 3
  • 13
  • 23
  • Hi! Thanks, is it possible to sort out if a product has 2 specific groups? For example search for those products that has group AA AND BB - but not another group – Henrik Rosqvist Mar 01 '23 at 14:26
1

You can use the following array formula in D1 cell, that generates the entire result at once:

=LET(A, A2:A9,B,B2:B9,Aux,UNIQUE(A),Bux,UNIQUE(B),ones,SEQUENCE(ROWS(Bux),,1,0),
 cnts,MMULT(COUNTIFS(A, Aux,B, TOROW(Bux)),ones),
 f,FILTER(HSTACK(Aux, cnts),cnts=1), VSTACK({"Product","Counts"}, f))

Here is the output: excel output1

We are filtering by the number of counts, but if you remove the filter you can get counts for all products.

Bonus

Responding to @HenrikRosqvist comment in @user11222393's answer to find products that belong to specific group. Let's say we have as lookup the groups delimited by comma (,) in cell: H2, you can use the following formula:

=LET(A, A2:A9,B,B2:B9, grs, TEXTSPLIT(H2,","),
 UNIQUE(FILTER(A, ISNUMBER(XMATCH(B, grs)))))

Here is the output: excel output2

David Leal
  • 6,373
  • 4
  • 29
  • 56
  • Fantastic :) Exactly what Iam looking for - unfortunately I have excel 2016. Does the above formulas have some equivalents in XL2016? – Henrik Rosqvist Mar 03 '23 at 07:25
  • @HenrikRosqvist you need to indicate it in your question adding the corresponding tag, otherwise people don't assume any excel constraint in your question. 2016 is too old you don't have the handy function such as `SEQUENCE`, `FILTER`, `UNIQUE`, etc. So a totally different approach needs to be taken, I am not familiar with the old way of doing things in excel, maybe other people from this community can help you more. Anyway way if you consider my answer valuable, you can upvote it. Another alternative is to use MS Excel Web, it is free. – David Leal Mar 03 '23 at 17:46