COUNT involving several columns

[nsd3]

Hi, I need to find a formula on cell A2 on the attached file. The formula should show me the count of Customers who buy SKU 1 only.

I tried to use filters across SKU (2-16) where there is "0" and then count all customers who have values >0 on Column B. But this takes a long time and I have to do similar tables quite a few times. I was just hoping if anyone can come up with a more efficient solution to it.

Regards.

[DonkeyOte]

One route... create a key of 0/1 eg:

T5:
=SIGN(B5)&":"&SIGN(C5)&":"&SIGN(D5)&":"&SIGN(E5)&":"&SIGN(F5)&":"&SIGN(G5)&":"&SIGN(H5)&":"&SIGN(I5)&":"&SIGN(J5)&":"&SIGN(K5)&":"&SIGN(L5)&":"&SIGN(M5)&":"&SIGN(N5)&":"&SIGN(O5)&":"&SIGN(P5)&":"&SIGN(Q5)
copy down for all rows

Then you can use a simple and efficient COUNTIF... eg:

B2:
=COUNTIF($T$5:$T$100,SUBSTITUTE("0:0:0:0:0:0:0:0:0:0:0:0:0:0:0:0","0","1",COLUMNS($B1:B1)))
copied across to Q2

Adjust ranges to suit

[DonkeyOte]

I should also have added the formulae for calculating R & S based off this 0/1 approach, eg:

SKU 1-4
R2: =COUNTIF(T5:T100,"*1*:0:0:0:0:0:0:0:0:0:0:0:0")

SKU 5-16
S2: =COUNTIF(T5:T100,"0:0:0:0:*1*")

[martindwilson]

you could use some longish sumproducts for your example it shouldnt affect performance

[daddylonglegs]

Try this formula in B2 copied across

=SUMPRODUCT((B5:B100>0)*($R5:$R100+$S5:$S100=B5:B100))

[martindwilson]

i like it better the looooong way lol

[nsd3]

Thanks a lot guys. Appreciate that!