Counting active volunteers (some sort of COUNTIF maybe?)

[salange]

I have a spreadsheet where all the columns are dates and each row is the number of shifts logged by a given volunteer on each date. See <A HREF="http://www.awarewildlife.org/wp-content/uploads/2018/03/example-active-volunteers.jpg">cropped screenshot here</A>. I want a formula to go in AL1, AM1, AN1, etc, that will tell me how many volunteers were "active" on those dates, where "active" is defined as "logged at least one shift between that day and the three preceding days." So AL1 should return 2 since row 6 and 9 both logged one shift in the four day period. AM1 should also return 2 since row 7 became active but row 9 became inactive. AN1 should return 3 since row 9 became active again while rows 6 and 7 stayed active.

Any help would be very much appreciated!

 

[Perpa]

Salange,

Copy the following formula into cell AL1:

 =COUNTIF(AI$6:AL$9,1)

Then copy and paste into cells AM1 and AN1. The formula will be updated when you paste into the new cells.
The columns will be updated and the '$' will maintain the rows referenced, 6 to 9.
Perpa

 

[salange]

Salange,

Copy the following formula into cell AL1:

 =COUNTIF(AI$6:AL$9,1)

Then copy and paste into cells AM1 and AN1. The formula will be updated when you paste into the new cells.
The columns will be updated and the '$' will maintain the rows referenced, 6 to 9.
Perpa

Thank you for the suggestion, but that doesn't do what I want (perhaps due to a poor explanation on my part). That code counts all the 1s in the range, so if the volunteer on row 6 works two "1" shifts in the range, the formula returns 2. What I want is to count whether the volunteer is "active," so that should only return 1. In other words, I don't want to count all the shifts, I just want to count how many volunteers showed up at least once.

Thanks to you or anyone else who may be able to guide me further!

 

[Perpa]

Salange,
I assumed the volunteer names are listed in column AF, change the column to suit.
The following code is a Worksheet_SelectionChange type, and must be entered as a Worksheet module, not a standard module.
To enter the code, Right Click on the Sheet tab where your table is located, select 'View Code', select 'Worksheet' from the dropdown. Note that the first and last lines of code have been auto-entered for you. Then paste the code below (between the first and last lines) into the large window. Make sure not to duplicate the first and last lines. Then close the window and save the workbook as macro enabled.

To run the code just select any cell in row 1, the value you want will be shown therein.
Perpa

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Row = 1 Then
    Dim cnt As Integer
    Dim col, rw, lr As Long
    
    cnt = 0
    lr = Cells(Rows.Count, "[COLOR=#ff0000]AF[/COLOR]").End(xlUp).Row
    
    For rw = 6 To lr
        For col = Target.Offset(0, -3).Column To Target.Column
           If Cells(rw, col) <> "" Then
               cnt = cnt + 1
               GoTo skip
           End If
        Next col
skip:
    Next rw
    
    Target.Value = cnt
End If
End Sub

 

[Peter_SSs]

What I want is to count whether the volunteer is "active," so that should only return 1. In other words, I don't want to count all the shifts, I just want to count how many volunteers showed up at least once.

Try this standard worksheet formula, copied across (to the right and/or left).

Excel Workbook
	AI	AJ	AK	AL	AM	AN	AO	AP	AQ
1				2	2	3	2	2	1
2
3
4
5
6	1		1
7					1
8
9	1					1
Count Active

 

[salange]

Thank you both! I tried Peter's standard formula first and it worked like a charm. Y'all have indirectly helped us save more wildlife - I run a wildlife rehabilitation nonprofit - thank you!!

 

[Peter_SSs]

You are welcome. Good luck with your wildlife project!