|
Calculate how many customers take SKU's
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é |
Calculate how many customers take SKU's
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é |
Calculate how many customers take SKU's
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é |
Calculate how many customers take SKU's
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é |
Calculate how many customers take SKU's
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 |
Calculate how many customers take SKU's
|
Calculate how many customers take SKU's
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 --- |
Calculate how many customers take SKU's
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é |
Calculate how many customers take SKU's
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 --- |
Calculate how many customers take SKU's
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 --- |
Calculate how many customers take SKU's
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 --- |
Calculate how many customers take SKU's
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 --- |
Calculate how many customers take SKU's
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 |
Calculate how many customers take SKU's
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 |
| All times are GMT +1. The time now is 04:25 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com