JohnnyB
New Member
- Joined
- Jun 24, 2002
- Messages
- 4
Hello All,
I've been asked to add a formula to summarize data in a workbook that looks like this:
The summary will go on another sheet in the same workbook. My users want monthly summaries for each sales rep. Too keep things as simiple as possible,let's say summary sheet will have reps going down Column A and Months going across Row 1. The formula in Cell B2 of the summary should have Peter B's sales for the month of January. Please note: The detail sheet has varying amounts of sales for each rep in each month. So I'm thinking I should do a formula that says something like "If (Detail!C2:C25=A2 and if Detail!B2:B25=B1), Sum(D2:D25). So Peter B's total for January should be 6,603,516.48 since he's the only one who sold in January, but his Feb total should be $3,880,323.
Thanks in advance!
John
I've been asked to add a formula to summarize data in a workbook that looks like this:
| SummarySample.xls | ||||||
|---|---|---|---|---|---|---|
| A | B | C | D | |||
| 1 | Signed Date | Company | SalesRep | Volume | ||
| 2 | January-05 | Company A | Peter B | $2,726,222.40 | ||
| 3 | January-05 | Company B | Peter B | $90,874.08 | ||
| 4 | January-05 | Company C | Peter B | $3,786,420.00 | ||
| 5 | January-05 Total | $6,603,516.48 | ||||
| 6 | February-05 | Company D | Bill P | $45,437 | ||
| 7 | February-05 | Company E | Robert I | $757,284 | ||
| 8 | February-05 | Company F | Peter B | $2,653,523 | ||
| 9 | February-05 | Company G | Peter B | $1,226,800 | ||
| 10 | February-05 Total | $4,683,044 | ||||
| 11 | March-05 | Company H | Marie C | $265,049 | ||
| 12 | March-05 | Company I | Marie C | $90,874 | ||
| 13 | March-05 | Company J | Peter B | $7,572,840 | ||
| 14 | March-05 Total | $7,928,763 | ||||
| 15 | April-05 | Company K | Bill P | $90,874 | ||
| 16 | April-05 | Company L | Bill P | $136,311 | ||
| 17 | April-05 | Company M | Bill P | $210,784 | ||
| 18 | April-05 | Company N | Bill P | $136,311 | ||
| 19 | April-05 | Company O | Robert I | $567,963 | ||
| 20 | April-05 | Company P | Robert I | $1,200,000 | ||
| 21 | April-05 | Company Q | Robert I | $726,993 | ||
| 22 | April-05 | Company R | Robert I | $2,271,852 | ||
| 23 | April-05 | Company S | Marie C | $1,135,926 | ||
| 24 | April-05 | Company T | Paul N | $3,029,136 | ||
| 25 | April-05 | Company U | Peter B | $1,817,482 | ||
Detail | ||||||
The summary will go on another sheet in the same workbook. My users want monthly summaries for each sales rep. Too keep things as simiple as possible,let's say summary sheet will have reps going down Column A and Months going across Row 1. The formula in Cell B2 of the summary should have Peter B's sales for the month of January. Please note: The detail sheet has varying amounts of sales for each rep in each month. So I'm thinking I should do a formula that says something like "If (Detail!C2:C25=A2 and if Detail!B2:B25=B1), Sum(D2:D25). So Peter B's total for January should be 6,603,516.48 since he's the only one who sold in January, but his Feb total should be $3,880,323.
Thanks in advance!
John
