Help with worksheet and math functions

[Guest]

I have cell B7 and down the column with customer names. Cell C7 and down the
column will be the sales rep listed for the customer where some of the cells
for c7 will be blank. Cell d7 and down will be the sales number for that
customer. I have cell A1 to A6 with the sales reps names. I want to get the
total of sales for each rep in A1, A2, A3 and so from the customer data. One
of the reps will be getting the data will no info in c7 and down where the
others will correspond with the rep name.

Thank you

 

[Guest]

Is this anything close to what you are starting with? If so, how do you want
to manipulate the data?


Company Rep # of Sales
===========================================
ABC, Inc Mickey Mouse 100
DEF, Inc 111
GHI, Inc Mr. Spock 77
JKL, Inc Marylin Monroe 40
MNO, Inc 104

 

[Guest]

Here you go

Rep
Mickey Mouse 100
Mr. Spock +No Rep 223
Marlin Monroe 144
a b
c
Company Rep # of Sales
===========================================
4 ABC, Inc Mickey Mouse 100
5 DEF, Inc 111
6 GHI, Inc Mr. Spock 77
7 JKL, Inc Marylin Monroe 40
8 MNO, Inc Marylin Manroe 104
9 KLO, Inc
10
10BNC, Inc Mr. Spoc 25


a b
1 Mickey Mouse 100
2 Mr. Spock +No Rep 223
3 Marlin Monroe 144

I am wanting to know how to write the fromula in b1, b2 and b3 to reflect
the data from C4 - C10.

 

[Guest]

The only simple solution I can offer is the sumif function. For a concise
example, consider the following data set:


A B C
ABC, Inc Mickey Mouse 100
DEF, Inc Mickey Mouse 50
GHI, Inc Marilyn Monroe 100
JKL, Inc Homer Simpson 30

For Mickey Mouse, choose the output cell and type
=SUMIF(B1:B4,"Mickey Mouse",C1:C4)

and you should get 150.

This assumes you would create an output cell for each sales rep and that
each name is unique. To sum the blank ones, replace "Mickey Mouse" with "".

 

[Guest]

Great that part fixes my problem but one more issue and I am done. I know
that you use the (Worksheetname!) to carry data from one worksheet to
another so if this data is on worksheet customers how would I write the
fromula to put it in worksheet NoName? Thank you for your help

 

[Ken Johnson]

Great that part fixes my problem but one more issue and I am done. I know
that you use the (Worksheetname!) to carry data from one worksheet to
another so if this data is on worksheet customers how would I write the
fromula to put it in worksheet NoName? Thank you for your help

=SUMIF(Customers!B1:B4,"Mickey Mouse",Customers!C1:C4)

If Reps names are in Column A on the NoName sheet and if Mickey Mouse's
name is in A1, then...

=SUMIF(Customers!B1:B4,A1,Customers!C1:C4)

(=SUMIF(Customers!B1:B4,NoName!A1,Customers!C1:C4) also works, but the
NoName! part of the A1 address is not necessary)

Ken Johnson