sumproduct

[Gary]

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.

 

[Sheeloo]

Try
=ROUNDDOWN(SUMPRODUCT(ROUNDDOWN(I10:GC10/5,0)),0)

 

[Sheeloo]

=SUMPRODUCT(ROUNDDOWN(B77/5,0))
will do the trick
second rounddown is not required...

 

[T. Valko]

I *think* this does what you want:

=SUMPRODUCT(--(I10:GC10>=5),INT(I10:GC10/5))

 

[Gary]

Thanks! Works like a charm. You're Good!!

=SUMPRODUCT(ROUNDDOWN(B77/5,0))
will do the trick
second rounddown is not required...

 

[T. Valko]

=SUMPRODUCT(--(I10:GC10>=5),INT(I10:GC10/5))

Improvement

We can shorten that by a few keystrokes:

=SUMPRODUCT(INT(B1:H1/5))