Hi,
How can how calculate the number of total occurrences where "off" occurs when "off" can be repeated several times in a row?
Example
Col A
off
off
on
off
on
off
off
off
on
off
The answer would be 4 because I only want to count the first off and then move on to when off occurs again after a on
Thanks
Kris
like this ? =IF(INDEX(A:A;MATCH(REPT("Z";255);A:A))="ON";COUNTIF(A:A;"ON");COUNTIF(A:A;"ON")+1) (replace semi-colons with commas if needed)
Or this confirmed with ctrl+shift+enter (not just enter)
Formula:
Please Login or Register to view this content.
If your data is in the Range of "A1:A10" use below formula
=SUM((FREQUENCY((A1:A10="Off")*ROW(A1:A10),(A1:A10<>"Off")*ROW(A1:A10))>0)*1)-(COUNTIF(A1:A10,"Off")<>ROWS(A1:A10))
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks