Calculate how many customers take SKU's

D

Des

Hi
I have 9 columns, each with a SKU no starting series 52000 though 52008
Rows are customer no and name

I want to calculate how many customers take 1 SKU, 2SKU's, 3SKU's etc and
put the result either on another tab, or at the end of the columns as listed
above.

How would I go about achieving that?

Many thanks
Desiré
 
M

MarkN

Hi Desiré,

Assuming your names are A1:A10 and your SKU numbers are in B1:B10, create a
list of client names down a column (I have started at F2) and in the next
column over (G2 in my case), try this formula:

=SUMPRODUCT(--($A$1:$A$10=F2),--(LEFT($B$1:$B$10,3)="SKU"))
 
D

Des

Hi Mark

Thanks for prompt response.

The table looks something like this

52000 52001 52002 52003
Cust No Name Units
111 Co A 20 10 0 30
112 Co B 10 0 20 5
etc

Unfortunately this is how it's set out and it comes directly from our
system, so I don't want to go another route with formatting it differently
(customer list is several 1000). I have many other variables to this as
well, like adding say a Division, Channel etc to each customer and these
would typically be inserted in Column A, B with Cust No and Name after that.

Cust No A2:A11
Cust Name B2:B11
SKU C1:K1

I'm guessing that the suggested formula needs to change based on the above?
Apologies, I should have made this clearer.

I do tons of this sort of analysis - and do it in a roundabout way, but it's
time consuming and time for me to learn to do it properly!

Thanks, Des
 
M

Max

One way ..

Assuming data in cols C to K, from row2 down with no blank cells (cells will
house zeros as per sample posted)

Put in L2:
=COUNTIF(C2:K2,"<>0")
Copy down

Then with the numbers: 1 - 9 listed in say N2:N10
Put in O2: =COUNTIF(L:L,N2)
Copy down to O10. O2:O10 will return the required customer counts by number
of SKUs listed in N2:N10, viz. your line:
 
M

Max

If needed, for completeness
Then with the numbers: 1 - 9 listed in say N2:N10 ...
Click to expand...

just expand the summary accordingly to include customers with no orders (all
zeros)
 
D

Des

Hi Biff

I'm wanting to know how many SKU's each customer is taking, out of a
possible 4 as set out in this example

Thanks
Des
 
D

Des

Hi Max
Just ran my report and trying to work this out - and realized that I do in
fact have blank cells (depending on what data I pull).

How would the formula be altered to consider blanks as a 0?

Thanks
Des
 
D

Des

Thank you so much - that worked great!
The only error I found was that the initial formula of COUNTIF(C2:K2,"<0")
should have been > instead of <
Didn't seem to have a problem with blanks either!

Can't thank you enough everyone - saved me hours of work!
Des
 
D

Des

Hi Max

Okay - now that I have this done, I know need to calculate what the ave no
of units is by no of SKU's taken.

My reports reads like this
52000 52001 52002 52003 | 52000 52001 etc Ave
Cust No Name Count | Units
Units
111 Co A 20 10 0 30 | 12 36
24
112 Co B 10 0 20 5 | 144 216
180
etc

So, having calculated per your suggestion, I need to calc what the ave
units per "how many customers take 1 SKU, 2 SKU's etc" is.

I'd be happy to send an excerpt of the spreadsheet, if that would help.

Any advice would be greatly appreciated.
Thanks
Des
 
Top