Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
I have a system to reward sales people with 1 point each time they make 5
phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
Try
=ROUNDDOWN(SUMPRODUCT(ROUNDDOWN(I10:GC10/5,0)),0) "Gary" wrote: I have a system to reward sales people with 1 point each time they make 5 phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
=SUMPRODUCT(ROUNDDOWN(B7:D7/5,0))
will do the trick second rounddown is not required... "Sheeloo" wrote: Try =ROUNDDOWN(SUMPRODUCT(ROUNDDOWN(I10:GC10/5,0)),0) "Gary" wrote: I have a system to reward sales people with 1 point each time they make 5 phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
I *think* this does what you want:
=SUMPRODUCT(--(I10:GC10=5),INT(I10:GC10/5)) -- Biff Microsoft Excel MVP "Gary" wrote in message ... I have a system to reward sales people with 1 point each time they make 5 phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
Thanks! Works like a charm. You're Good!!
"Sheeloo" wrote: =SUMPRODUCT(ROUNDDOWN(B7:D7/5,0)) will do the trick second rounddown is not required... "Sheeloo" wrote: Try =ROUNDDOWN(SUMPRODUCT(ROUNDDOWN(I10:GC10/5,0)),0) "Gary" wrote: I have a system to reward sales people with 1 point each time they make 5 phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
sumproduct
=SUMPRODUCT(--(I10:GC10=5),INT(I10:GC10/5))
Improvement We can shorten that by a few keystrokes: =SUMPRODUCT(INT(B1:H1/5)) -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... I *think* this does what you want: =SUMPRODUCT(--(I10:GC10=5),INT(I10:GC10/5)) -- Biff Microsoft Excel MVP "Gary" wrote in message ... I have a system to reward sales people with 1 point each time they make 5 phone calls in a day. If they make 10 phone calls they get 2 points. If they made 9 calls, it's only 1 point. I have a cell for each day of the week in a row for a particular salesperson. I enter the number of calls that person makes each day and want to know how many points to award a person based upon how many phone calls are made in groups of 5 each day. 1 call 0 points, 5 calls 1 point, 12 calls 2 points, 15 calls 3 points, 16 calls 3 points, 25 calls 5 points, etc. I need to count the number of times cells in a row contain a number = to 5 and if greater than 5, I need to count how many times 5 is divisible into the number of calls made each day. I tried =COUNTIF(I10:GC10,"4"), but this only tells me the number of time the sales person made at least 5 calls in a day and does not account for more than 5 calls in a day. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
Help with SUMPRODUCT | Excel Discussion (Misc queries) | |||
Sumproduct..help please | Excel Worksheet Functions |