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é

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"))
--
Hope this helps,
MarkN


"Des" wrote:

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é

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



"MarkN" wrote:

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"))
--
Hope this helps,
MarkN


"Des" wrote:

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é

Based on your sample data:

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

What kind of results are you wanting?

Biff

"Des" wrote in message
...
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



"MarkN" wrote:

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"))
--
Hope this helps,
MarkN


"Des" wrote:

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é

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:
.. how many customers take 1 SKU, 2SKU's, 3SKU's etc
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Des" wrote in message
...
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

Leave L1 blank
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

just expand the summary accordingly to include customers with no orders (all
zeros)
Then with the numbers: 0 - 9 listed in say N2:N11
Put in O2: =COUNTIF(L:L,N2)
Copy down to O11 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"Biff" wrote:

Based on your sample data:

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

What kind of results are you wanting?

Biff

"Des" wrote in message
...
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



"MarkN" wrote:

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"))
--
Hope this helps,
MarkN


"Des" wrote:

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é

Thanks Max - I'll give that try.

"Max" wrote:

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

just expand the summary accordingly to include customers with no orders (all
zeros)
Then with the numbers: 0 - 9 listed in say N2:N11
Put in O2: =COUNTIF(L:L,N2)
Copy down to O11 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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


"Max" wrote:

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

just expand the summary accordingly to include customers with no orders (all
zeros)
Then with the numbers: 0 - 9 listed in say N2:N11
Put in O2: =COUNTIF(L:L,N2)
Copy down to O11 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

"Max" wrote:

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

just expand the summary accordingly to include customers with no orders (all
zeros)
Then with the numbers: 0 - 9 listed in say N2:N11
Put in O2: =COUNTIF(L:L,N2)
Copy down to O11 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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



"Max" wrote:

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

just expand the summary accordingly to include customers with no orders (all
zeros)
Then with the numbers: 0 - 9 listed in say N2:N11
Put in O2: =COUNTIF(L:L,N2)
Copy down to O11 ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Des,
Glad you got that working.
Please put in your new query as a fresh posting
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Des wrote:
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

Hi Max

Thanks everyone and will do
Des

"Max" wrote:

Des,
Glad you got that working.
Please put in your new query as a fresh posting
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
Des wrote:
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