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
Medium Priority
519 Views

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
Question by:hballard
• 3
• 3

LVL 81

Expert Comment

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

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

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.

0

Author Comment

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.

Harrison
0

LVL 81

Accepted Solution

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?

0

Author Comment

ID: 8236210

The solution is simple and effective.

Thank you for your time and patience.

Harrison
0

## Featured Post

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…
###### Suggested Courses
Course of the Month11 days, 10 hours left to enroll