Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel 2002 Formula with multiple subtotals

Posted on 2003-03-30
6
Medium Priority
?
519 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

While Plesk offers many potential benefits to website administrators, including compatibility with Windows Server and other leading technologies, the company has also been working to differentiate it from other control panels for content management…
Choosing the right mix of apps is very much necessary for CPAs for making the most of the latest technology through which they can boost their growth.
The viewer will learn how to successfully create a multiboot device using the SARDU utility on Windows 7. Start the SARDU utility: Change the image directory to wherever you store your ISOs, this will prevent you from having 2 copies of an ISO wit…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

564 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