?
Solved

Excel 2002 Formula with multiple subtotals

Posted on 2003-03-30
6
Medium Priority
?
512 Views
Last Modified: 2008-02-01

Can anyone help me create a formula that calculates cells based on multiple subtotals?
    (I am using Windows XP and Excel 2002 SP2)


Objective:  Create a formula in Column G that subtotals Column D (Sales Reps Revenue)
              when "like" departments in Column A.  
                   For each row in Column G contains: The Sales Reps Department Subtotal
                     times Column E [# of Sales Calls] minus Column D [Revenue]  

Excel Sheet:  Column A - Department Number,
              Column B - Dept Mgr Name,
              Column C - Sales Rep Name,
              Column D - Sales Rep's Revenue,
              Column E - contains Number of Sales Calls  
              Column F - Blank
              Column G - contains new formula    

The sheet is sorted by Department number and Sales Rep name.
There are about 20 departments and a variable number of Sales Reps per department.


Thank you
0
Comment
Question by:hballard
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 81

Expert Comment

by:byundt
ID: 8234867
Although your problem seems tailor-made for Pivot-Tables, the SUMIF function will create the subtotal you requested:

=SUMIF(A$2:A$40,A2,D$2:D$40)
0
 

Author Comment

by:hballard
ID: 8235154
Thank you very much for your quick response.

Yes it creates the subtotal, but the subtotal is only part of the formula. What is the syntax for including the subtotal into whole formula? (I'm new to Excel)

   (Subtotal x Column E) - Column D

Note:
  This calculated column will become part of a pivot table.  I'm performing the calculation in the spreadsheet because there seems no way to address the calculated total fields in the pivot table.

Thanks again for your time and patience.      
0
 
LVL 81

Expert Comment

by:byundt
ID: 8235732
It looks like most of my comment got truncated. I was assuming that your range of interest was A2:G40

To complete the formula, if I understand you correctly, you want:

=SUMIF(A$2:A$40,A2,D$2:D$40) *E2-D2

This will multiply the subtotal by the number of sales calls for that rep, then subtract that rep's sale revenue. If this is not what you want, please try explaining it again.

Brad
0
Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

 

Author Comment

by:hballard
ID: 8235985
Thanks Brad, this formula works for a single department.  

But I need to account for the changing departments or
multiple subtotals in the formula.

That is to programmatically "sense" a change in department and re-calculate for the new department

Column A      Column G        
Dept 1        (Subtotal Dept 1) x Column E - Column D
Dept 1        (Subtotal Dept 1) x Column E - Column D
Dept 1        (Subtotal Dept 1) x Column E - Column D
Dept 1        (Subtotal Dept 1) x Column E - Column D
Dept 2        (Subtotal Dept 2) x Column E - Column D
Dept 2        (Subtotal Dept 2) x Column E - Column D
Dept 3        (Subtotal Dept 3) x Column E - Column D
Dept 3        (Subtotal Dept 3) x Column E - Column D
Dept 3        (Subtotal Dept 3) x Column E - Column D
Dept 3        (Subtotal Dept 3) x Column E - Column D
Dept 3        (Subtotal Dept 3) x Column E - Column D

  .... and so on.

The formula must first subtotal column D for all "like" departments codes [in column A] and then use that result in the formula [in column G].    

The sheet is sorted by Department number and Sales Rep name.  There are about 20 departments with a variable number of Sales Reps per department.

I hope this explanation is clearer.  

Thanks again for your assistance.

Harrison
0
 
LVL 81

Accepted Solution

by:
byundt earned 1000 total points
ID: 8236068
Harrison,

In my test worksheet, each row contained the subtotal of all the instances of Dept 1 in the entire range. For example, using a, b & c for the department names:

A      D      E     Subtotal     G
a     10      1      35            25
b     20      2      30            70
c     40      4      40          120
a     20      2      35            50
b     10      3      30            80
a       5      5      35          170

The dollar signs ($) in the formula are there so you can copy the formula down column G from top to bottom of the range.

Now the bit about multiplying by column E and subtracting column D is still a little confusing to me. The formula I previously suggested:

=SUMIF(A$2:A$40,A2,D$2:D$40) *E2-D2

gives the results shown above in column G. Is that what you wanted?

Brad
0
 

Author Comment

by:hballard
ID: 8236210
Brad,

The solution is simple and effective.

Thank you for your time and patience.

 Harrison
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Skype is a P2P (Peer to Peer) instant messaging and VOIP (Voice over IP) service – as well as a whole lot more.
All of the resources available today make learning a new digital media easier than ever-- if you know where to begin. This is a clear, simple guide to a few of the basic digital art mediums and how to begin learning them on your own.
XMind Plus helps organize all details/aspects of any project from large to small in an orderly and concise manner. If you are working on a complex project, use this micro tutorial to show you how to make a basic flow chart. The software is free when…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question