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?

Answer Wiki

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

——–

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    It could help if you let us know how you have your information organized. What information is in what columns and rows, and how you identify the group each person belongs to.
    69,920 pointsBadges:
    report
  • Rbecker69
    Here's my example: Salesperson JAN FEB MAR INDIVIDUAL TOTAL Don (T2) 4 6 6 16 David (T1) 8 4 3 15 Saundra (T3) 7 5 3 15 Janet (T1) 2 5 7 14 Jerry (T3) 5 2 6 13 Carrie (T2) 1 7 4 12 Rebecca (T3) 3 4 4 11 Alice (T2) 2 3 5 10 Russell (T1) 4 4 1 9 Cell A1 is Salesperson header, A2 is JAN, etc. Cell A2 is salesperson 1 name (sorted by column E [individual total]) Cell A3 is salesperson 2 name... T1, T2 & T3 designate which team each salesperson is on. I need to be able to calculate the total sales per team. Each month, the individual total will need to be sorted in descending order, therefore the salesperson names won't be in the same order.
    15 pointsBadges:
    report
  • Chippy088
    You need to have a column for the team, as it is a variable you are operating on, it is not easy to sort the way you have it now, unless you are proficient with the instring function. So your headings should be something like this Salesperson,Team, JAN,FEB,MAR,INDIVIDUAL TOTAL Cell A1 is Salesperson header, A2 is Team, etc. Then the sumif() can be used on the team column for cumulative totals, and a second sumif() can be used to total individual sales. Also Pivot tables are now possible.
    4,625 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following