im trying to find a formula that will count consecutive cells in a row as one to be used for attendance tracking. for instance if the data is a,a,0,0,a,0,0,0,a,a,a the count should come out 3 since it was 3 times a came up with consecutive days counting as one.
=SUM((FREQUENCY(IF(A1:K1="a",COLUMN(A1:K1)),IF(A1:K1<>"a",COLUMN(A1:K1)))>0)*1)
Try this array formula
Confirm with Shift+Ctrl+Enter
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Hi.
Based on data in A1:K1, array formula**:
=SUM(IF(FREQUENCY(IF(A1:K1="a",COLUMN(A1:K1)),IF(A1:K1<>"a",COLUMN(A1:K1))),1))
Regards
**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).
that works great thanks
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks