|
Sumif And Countif
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 Can anyone help me with this problem? Thanks, roter |
Sumif And Countif
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. -- If this post helps, pls click Yes --------------- TGV "roter789" wrote: 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 Can anyone help me with this problem? Thanks, roter -- roter789 |
Sumif And Countif
=SUMIF(A:A,"BERNIER",B:B)
-- __________________________________ HTH Bob "TGV" wrote in message ... 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. -- If this post helps, pls click Yes --------------- TGV "roter789" wrote: 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 Can anyone help me with this problem? Thanks, roter -- roter789 |
Quote:
Here is the formula i incorporate in the previous formula:=SUMIF(Name,COUNTIF(Name,SUM(Sales)),Sales ) Thanks, Reuben |
Sumif And Countif
I don't know what you mean or what you think that formula does.
-- __________________________________ HTH Bob "roter789" wrote in message ... 'Bob Phillips[_3_ Wrote: ;868684']=SUMIF(A:A,"BERNIER",B:B) [/i][/color] 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 -- roter789 [/i][/color] |
Sumif And Countif
Hello,
I suggest to use my UDF Sfreq: http://sulprobil.com/html/sfreq.html Don't waste your time with SUMPRODUCT: http://sulprobil.com/html/sumproduct.html Regards, Bernd |
Sumif And Countif
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 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) |
Quote:
|
| All times are GMT +1. The time now is 05:26 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com