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.

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.

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.

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.

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.

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.