Grouping table data into smaller table

Hi there,
I'm hoping anyone could enlighten me on how to group a list of data in a table, into a new table with grouped data, WITH the sum added up.

For instance,
<Subject><Input><Outcome>
AA-100-6200
AB-150-4100
BA-450-8000
BB-600-2150
...

Into a new table where all are categorized into the first alphabet,
<Subject><Input><Outcome>
A-250-10300
B-1050-10150
...

Many thanks!

Put this formula in F2:

=SUMIF($A:$A,$E2&"*",B:B)

and it should copy down automatically.

Copy into G2, and again it should fill the cells below.

Hope this helps.

Pete

It doesn't matter what order the original table is in - try it by sorting the first table on the second field.

Hope this helps.

Pete

Fair enough, Pete.
But the problem is for instance

A with the value of 10,211, is the total of AAM, ABB, ACG, AD, ASA, ASWQ and AYR.

Say that I replace 'A' into John, or even I replace 'AAM' into 'Peter', the number will go off.
(i.e., AAM being 206, the subject is being replaced to 'Peter', A will turned into 10,005, because 206 is taken away from 10,211)

Reason that I would like it to be automated for the second table is because I will need to have the graph automatically generated, sales according to the sales manager, once there are changes to even a single salesperson.

I have the file attached again, this time it's clearer.

The main issue is that I will be receiving the packages sold and sales number pretty much daily and weekly, but there's only <name>, <packages sold> and <sales (revenue)>.

Hence, I would definitely have to put it into Excel every time I receive the figure report, especially the <name>, <packages sold> and <sales (revenue)>.

Now the main issue is that, it would be easy and hassle-free if I were to create a chart showcasing all the salesperson and their figures, but it's almost ridiculous since there's about 100+ salesperson in reality, hence it's better to segregate it via team, team that show the total packages sold for the team-members and the total sales generated.

Could this actually be done in Excel?

Originally Posted by FlameRetired

Then in column G of the summary table do this formula

Formula:

Please Login or Register  to view this content.

and one like it in column H

Formula:

Please Login or Register  to view this content.

Thanks FlameRetired, this is under the assumption that the column for 'Team Leader' is perfectly sorted. What if.... say, the reporting team have the name scattered, or even some names that are missing (i.e. Daniel and Anna's figures are yet to be confirmed, hence their name is not there?)

Is there such function whereby like
If Column A (Name) is 'Anna', Column B (Team leader) is 'Kenny' and so on? If there is, then this formula would be perfect for the bar chart generation for all the team.

As in, program the Excel to recognize the name into its respective Team Leader.

PS:Many thanks for all the time and effort!

Originally Posted by FlameRetired

The order of team leaders won't effect the totals. And since the conditional in the SUMIF formula is team leaders (the common link) if team members (not leaders) are missing from the main data table they just won't be included in the sum until their figures are tallied. However the rest of the team members numbers will be tallied. All the names can be scattered up and down that main data table. That's one of the things that makes SUMIF(S) so powerful. It sorts all that stuff out.

Precisely, Flame.
I can see and understand that the figures are all good even when the names are scattered.

But my point is that, what if my finance department or so, were to provide me a raw list of salesperson with their packages sold along with sales (revenue) without their respective team leader in the column next to the salesperson? Does it mean I will have to manually key in the team leader for each of the salesperson?

Bear in mind this is just a simplistic data with 21 salesperson, what if I have 100+ salesperson where I can't remember their respective team leader (20+ of them)?

Thanks.

On your last point: create a table of salespersons and respective team leaders. You could then use VLOOKUP (or similar) to fill in missing team Leaders.

Originally Posted by JohnTopley

On your last point: create a table of salespersons and respective team leaders. You could then use VLOOKUP (or similar) to fill in missing team Leaders.

Yes, JohnTopley! I've heard of people suggesting vlookup but I am totally clueless on how to work it out.

I have updated and attached another attachment with the salesperson and the directory in a separate table. Could you probably assist to put the vlookup formula so I could study it and apply it to all 100+ of my salesperson and 20+ sales manager/team leader?

Thanks all!

Put this formula in B2:

=IFERROR(VLOOKUP(Table1[[#This Row],[Name]],Table3,2,0),"not listed")

It should automatically copy down. You can add a Not Listed category at the bottom of Table 2 to check that totals all match.

Hope this helps.

Pete

Originally Posted by Pete_UK

Put this formula in B2:

=IFERROR(VLOOKUP(Table1[[#This Row],[Name]],Table3,2,0),"not listed")

It should automatically copy down. You can add a Not Listed category at the bottom of Table 2 to check that totals all match.

Hope this helps.

Pete

Good God, Pete!
Thanks a million! My saviour!
Now my sales performance report could be done easily especially when my manager wants it pretty much like instantly.