## Associate or link cells in Excel

15 pts.
Tags:
Microsoft Excel
I have a 30 person sales staff and I'm keeping track of the sale of one item on a monthly basis (31 rows, 13 columns). There are ten 3-person sales teams and I'm trying to get a team total for the 10 teams based on what each salesperson sells during a given month. I can total each individual's total sales, but I can't for the life of me get the team totals to populate correctly. I sort the total sales by individual from highest to lowest, so the top salesperson is always changing. Any ideas?

Thanks. We'll let you know when a new response is added.

I think you need SumIF. Example: =SUMIF(B\$21:B\$50,A1,C\$21:C\$50)

In that example I’m imagining that your 31 x 13 range starts in cell A20 (Row 20 contains column headings). In column A is the sales associate’s name or ID. In column B is the Team they belong to. In column C is the sales amount.

Now at the top of your sheet are the team names/IDs starting in A1. Put the formula in cell B1 and it will total all sales amounts for the team in cell A1. Put the second team name/ID in cell A2 and copy the formula to B2 – and so on.

If you have an older version of Excel – SumIF may not be available, in which case you can use Array Arithmatic. In this case, the formula is: {=SUM(IF(A\$21:A\$50=A1,B\$21:B\$50,0))}

You DON’T enter the curly brackets. Enter the formula without them and hit Shift-Ctrl-Enter (instead of just Enter).

You may want to consider a pivot table.

————

A pivot table could be your best option as suggested by Chatmaker, but you would have to put the team in a separate column (even if you decide not to use a pivot table, this separation is recommended).

In summary:

• To create the table, go to “Insert” and select “Pivot Table”.
• In the “create pivot table” dialog, select all your data as the range to be analyzed, and tell excel where you want to put the resultant table.
• If you want totals per team for the whole trimester, from the field list that appears to the right, drag the TEAM field to the ‘Row levels’ area, and drag the INDIVIDUAL TOTAL to the ‘Values’ area. If you want totals per team for a given month, say JAN, then instead of INDIVIDUAL TOTAL drag the JAN field to the ‘Values’ area.

You might want to try different options, this is just a simplified example.

Here is an article about creating pivot tables: Pivot Tables in Excel 2007

-CarlosDL

——–