Can I create an automatically updating list of order totals for a specific customer?

I am enhancing a spreadsheet for a friend of a friend, he initially wanted me to build him a table in his order summary list which summed up the order totals for each of his reps (about 7), one tab per month. This was solved using a simple SUMIF formula - I have attached a simplified sample of what I created for him

He then came back to me requesting that I do the same but for his customers. He has about 100 customers, and some of them only order once or twice a year so it's impractical to list every single customer and their total orders as 75% of them will have a NIL value. What I would love to create would be a list with the columns, customer - #of orders placed - total sales - average sales/order (etc etc etc) but would ignore any NIL values (see right hand side of attachment)

In other words, when the order list is empty, so is this separate summary, and then when I fill it with orders, the summary will list, customer A, customer B, D, X,Y,Z etc etc with their totals which is updated every time a new order is added to the list.

I have moderate experience with lookup tables etc, so if this kind of request requires a lot of advanced knowledge or just general faffing about (the old its 'easier said than done' routine); I'll just say to the guy 'whoa, this is way above me' and and tell him to buy some business software

Thank you for any help in advance

I google'd dynamic ranges, seems well within my knowledge and as nerdy as it sounds I'm always looking to learn new things that can be done with spreadsheets. If you could get me started it would be greatly appreciated but I'm not expecting anyone to do all the work for me!

Nice, thats exactly what I was after and more, thanks for completing the other columns too, i've always wanted to know how to 'count' cells - now I know!

Very kind of you, my friend will be very pleased

Hi milklizard88,

This is very simple to do in a pivot table. Enter a new row of data in the attached and the pivot table will automatically refresh.


Here is the code in the "March" sheet:

Please Login or Register  to view this content.

Originally Posted by twiggywales

glad to help,

please remember to mark as solved

Sorry, I'm new here, where's the button to mark solved?

EDIT: I guess I just put 'solved' in the thread title