Count stores that have 1 out of 5 Products, 2 Out of 5 etc

[PaterPen]

Hi All,

my first ever forum Post (in any Forum 0_0)

I have a list with stores that sell 5 different products. Some sell all 5, some none, and most any number in between. Now I want to count the stores that have 0 products, 1 product, 2 products and so on. It does not matter which product they sell, just that a certain number is present. The formula would probably be a combination of count(if....) but thats all I would know. The attached list is a neutralized example.

Help would be much appreciated!

[gak67]

The easiest way is with a helper column. This formula in M9 and dragged/copied down:

Please Login or Register  to view this content.

Then this formula in C1:

Please Login or Register  to view this content.

Copy that down to C6 then change the 0 to 1 in C2, the 0 to 2 in C3, etc.

[MarvinP]

Hi PaterPen and welcome to the forum,

See the attached with what I think you want. Yes a helper column seems appropriate for this problem.

Countstores Answer.xlsx

[PaterPen]

Great, works perfectly THANKS

[tim201110]

no helper column
=SUM(N(COUNTIF(OFFSET($C$8:$L$8,ROW($A$1:$A$50),),">0")=2*(ROW()-1))) as array formula