Converting data table into summary for sales performance

[Xsiv]

Excel Help Please.xlsx

I am hoping that one of you could help me with a data set/summary table I am working with (see attached). On the 1st tab, I have an ever increasing table of deals, broken down by salesperson and current status. I would like to summarize this data on tab 2, but am having a hard time on the cells highlighted yellow.

Basically, it is something like, "if salesperson = salesperson name then count status"
Additionally, for Col I ($ Funded), it is "if salesperson=column A (or salesperson name, sum if status = Funded"

I've tried pivot tables am not very skilled with them. Can a pivot table automatically update when new data is added to the Data Table tab or are the numbers fixed after the table is run? If not, a formula, as described above is probably the best way to go but I'm open to ideas.

Also, is there a way to for Row 7 on Salesperson Summary to automatically get added if a new (unique) salesperson is added to Col C of Data Table tab? If not, I can manually enter the new salesperson's name on A7 and then copy the formulas above.

Thank you in advance. Any insight would be great appreciated.

[aydeegee]

Hi Xsiv

Good news and bad news.

I have managed to do what you want, but the bad news is that it will only work with Excel 2010 and later. I note that your details say that you are using Excel 2003 (I remember it only dimly ) but I believe that sumifs (as opposed to sumif) was not available until 2010. However you have submitted an .xlsx file which indicated 2010 or later.

I attach a file that will work with Excel 2010 or later

As regards adding new salespersons, I would, on Data Table put in a lookup in column F (which could be hidden most of the time). Anytime a #N/A appears, you know the name is not on the Salespersons Summary. It is a simple operation to type the Salespersons name in Salespersons Summary and copy the details from column B:K.

Regards
Alastair