You could use this short macro. (Open VBA by right clicking on sheet tab,
view code, paste this in)
Select the cell you want total in, then run this (Alt+F8, select macro)

Sub FindMyTotal()

For Each cell In Range(ActiveCell.Offset(0, -12), ActiveCell.Offset(0, -1))
If cell.Value = "Y" Then
total = total + PriorCell + 10
PriorCell = PriorCell + 1
Else
PriorCell = 0
End If
Next cell
ActiveCell.Value = total
End Sub
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Erika" wrote:

I have a spreadsheet with sales reps and I am tracking points if they make
their month or not. So for example

Jan Feb Mar April May June
John Doe Y N Y Y Y N
=10+0+10+11+12+0
Jane Smith N Y Y N Y Y
=0+10+11+0+10+11+12

I am trying to find a way to get a grand total - each month I come in the
spreadsheet and enter either a Y or a N. If they make their month they get
10 points, if they make their month the next month they get 11 points however
if they don't start over. The trick is if they don't make their month and
get a zero next they start back over at 10 and increase from there. I need
to track this for an entire year and keep a running total.

Any ideas?