Sumif And Countif

R

roter789

i have this problem: i want to sum all the sales of one person bu
occurred many times using the SUMIF function. But i am not sure how t
do it since i am new to using this function. i have tried this formula
=SUMIF(Sales,COUNTIF(Name,Sales),Name) but i am not sure if this i
right

For example:
Name Sales Region Commission Rate Commission
BERNIER $171,904 central 4.80% $8,251
BERNIER $171,904 north 4.90% $8,423
BERNIER $171,904 north 6.30% $10,830
BERNIER $171,904 north 6.00% $10,314
BERNIER $171,904 south 4.40% $7,564
BERNIER $171,904 west 4.50% $7,736
BERNIER $171,904 west 4.50% $7,736
BINCE $162,605 central 5.80% $9,431
BINCE $162,605 east 4.60% $7,480
BINCE $162,605 east 4.60% $7,480
BINCE $162,605 south 4.10% $6,667
BUSHBY $200,448 east 5.40% $10,824
BUSHBY $200,448 east 4.10% $8,218
BUSHBY $200,448 south 6.20% $12,428
BUSHBY $200,448 south 6.20% $12,428
BUSHBY $200,448 west 5.40% $10,824
BUSHBY $200,448 west 5.10% $10,223
CAMPBELL $259,500 north 4.30% $11,159
CAMPBELL $259,500 north 6.20% $16,089
CAMPBELL $259,500 north 4.30% $11,159
CAMPBELL $259,500 south 5.20% $13,494
CAMPBELL $259,500 south 5.20% $13,494
CAMPBELL $259,500 west 6.20% $16,089
CAMPBELL $259,500 west 6.30% $16,349

Can anyone help me with this problem?

Thanks,
rote
 
T

TGV

I assume that ur data starts from A1 and endwith E25, the same is given
below.

A B C D E

1 Name Sales Region Commission Rate Commission
2 BERNIER $171,904 central 4.80% $8,251
3 BERNIER $171,904 north 4.90% $8,423
4 BERNIER $171,904 north 6.30% $10,830
5 BERNIER $171,904 north 6.00% $10,314
6 BERNIER $171,904 south 4.40% $7,564
7 BERNIER $171,904 west 4.50% $7,736
8 BERNIER $171,904 west 4.50% $7,736
9 BINCE $162,605 central 5.80% $9,431
10 BINCE $162,605 east 4.60% $7,480
11 BINCE $162,605 east 4.60% $7,480
12 BINCE $162,605 south 4.10% $6,667
13 BUSHBY $200,448 east 5.40% $10,824
14 BUSHBY $200,448 east 4.10% $8,218
15 BUSHBY $200,448 south 6.20% $12,428
16 BUSHBY $200,448 south 6.20% $12,428
17 BUSHBY $200,448 west 5.40% $10,824
18 BUSHBY $200,448 west 5.10% $10,223
19 CAMPBELL $259,500 north 4.30% $11,159
20 CAMPBELL $259,500 north 6.20% $16,089
21 CAMPBELL $259,500 north 4.30% $11,159
22 CAMPBELL $259,500 south 5.20% $13,494
23 CAMPBELL $259,500 south 5.20% $13,494
24 CAMPBELL $259,500 west 6.20% $16,089
25 CAMPBELL $259,500 west 6.30% $16,349

Now paste this formula in F2 Cell =SUMIF(A:B,"BERNIER",B:B) u will get the
result. Change the criteria "BERNIER" to the person for whom u want the
sales total. Other wise u can refer the search criteria to a cell and type
the person name. That is =SUMIF(A:B,H1,B:B) in H1 type the sales person name
to know his sales report.
 
R

roter789

'Bob Phillips[_3_ said:
;868684']=SUMIF(A:A,"BERNIER",B:B)
[/i]
Click to expand...
[/i]
Click to expand...
Click to expand...
[/QUOTE][/QUOTE]

Thanks for the enlightening with the proper formula, but I need the
criteria for the SUMIF function be a NAME which can be changed whatever
the name is, meaning it does not have to be a specific name. How do i do
it? should i use the COUNTIF function?
Here is the formula i incorporate in the previous
formula:=SUMIF(Name,COUNTIF(Name,SUM(Sales)),Sales)

Thanks,
Reuben
 
B

Bob Phillips

I don't know what you mean or what you think that formula does.

--
__________________________________
HTH

Bob

roter789 said:
'Bob Phillips[_3_ said:
;868684']=SUMIF(A:A,"BERNIER",B:B)
[/i]
Click to expand...
[/i]
Click to expand...
Click to expand...
[/QUOTE]

Thanks for the enlightening with the proper formula, but I need the
criteria for the SUMIF function be a NAME which can be changed whatever
the name is, meaning it does not have to be a specific name. How do i do
it? should i use the COUNTIF function?
Here is the formula i incorporate in the previous
formula:=SUMIF(Name,COUNTIF(Name,SUM(Sales)),Sales)

Thanks,
Reuben
[/QUOTE]
 
M

MyVeryOwnSelf

i have this problem: i want to sum all the sales of one person but
occurred many times using the SUMIF function. But i am not sure how to
do it since i am new to using this function. i have tried this
formula: =SUMIF(Sales,COUNTIF(Name,Sales),Name) but i am not sure if
this is right

For example:
Name Sales Region Commission Rate
Commission BERNIER $171,904 central 4.80% $8,251
BERNIER $171,904 north 4.90% $8,423
BERNIER $171,904 north 6.30% $10,830
BERNIER $171,904 north 6.00% $10,314
BERNIER $171,904 south 4.40% $7,564
BERNIER $171,904 west 4.50% $7,736
BERNIER $171,904 west 4.50% $7,736
BINCE $162,605 central 5.80% $9,431
BINCE $162,605 east 4.60% $7,480
BINCE $162,605 east 4.60% $7,480
BINCE $162,605 south 4.10% $6,667
BUSHBY $200,448 east 5.40% $10,824
BUSHBY $200,448 east 4.10% $8,218
BUSHBY $200,448 south 6.20% $12,428
BUSHBY $200,448 south 6.20% $12,428
BUSHBY $200,448 west 5.40% $10,824
BUSHBY $200,448 west 5.10% $10,223
CAMPBELL $259,500 north 4.30% $11,159
CAMPBELL $259,500 north 6.20% $16,089
CAMPBELL $259,500 north 4.30% $11,159
CAMPBELL $259,500 south 5.20% $13,494
CAMPBELL $259,500 south 5.20% $13,494
CAMPBELL $259,500 west 6.20% $16,089
CAMPBELL $259,500 west 6.30% $16,349
Click to expand...

Here's an easy way, but it doesn't use the functions you mention.

With the names in column A, select that column and use
Data > Filter > AutoFilter
This lets you choose a person using a pull-down list in A1.

With sales numbers in column B, use this to calculate the sum:
=SUBTOTAL(109,B:B)
 
Top