Summarising data from multiple rows

[mardecl1]

Excel Grouping Data to Summarise

Hello



I am trying to find out the average number of different product SKUs that a list of customers buys from me.

I have a spreadsheet (attached) that lists each order across a row. Each account could appear multiple times because they may have ordered on several occasions, with different or the same SKUs on each order. The account number is in column A, the ordering month in column B and then all of the SKUs in C-AK, including number of cases ordered each time.

I am trying to figure out how to summarise the data for each customer to just show how many different SKUs they ordered across the whole period so that I can then work out the average for the whole base of customers.

Please can someone show me how to work out how many different SKUs each unique account number ordered?

(There are 30,000+ rows in the full spreadsheet but I've just uploaded a sample)

Thank you so much in anticipation of a solution....this is driving me mad!

[xor]

You can use a calculation intensive SUMPRODUCT formula as shown in AR2 (which may slow down Excel) or (better, if you can accept a helper column) formulas like shown in columns AU and AW.

[Pecoflyer]

Please do not cross post without links
http://www.excelforum.com/showthread...487&highlight=

Read this to understand why. Thanks

[mardecl1]

Hello,

Please can you advise how I add a link? Thank you

[Pecoflyer]

Simply copy / paste the link

[mardecl1]

[quote=mardecl1;108596]Excel Grouping Data to Summarise

Hello

I am trying to find out the average number of different product SKUs that a list of customers buys from me.

I have a spreadsheet (attached) that lists each order across a row. Each account could appear multiple times because they may have ordered on several occasions, with different or the same SKUs on each order. The account number is in column A, the ordering month in column B and then all of the SKUs in C-AK, including number of cases ordered each time.

I am trying to figure out how to summarise the data for each customer to just show how many different SKUs they ordered across the whole period so that I can then work out the average for the whole base of customers.

Please can someone show me how to work out how many different SKUs each unique account number ordered?

(There are 30,000+ rows in the full spreadsheet but I've just uploaded a sample)

Thank you so much in anticipation of a solution....this is driving me mad!

This query is also posted at http://www.excelforum.com/showthread.php?t=1168487&p=4554534&posted=1#post45 54534