LinkBack URL
About LinkBacks
Hoping this hasn't already been covered here. I need a way to count zeros in a row, but I need to count single zeros as one and consecutive zeros as 1 also.
So a row like this 0 1 0 0 1 1 would equal 2. Any help would be greatly appreciated.
Hoping this hasn't already been covered here. I need a way to count zeros in a row, but I need to count single zeros as one and consecutive zeros as 1 also.
So a row like this 0 1 0 0 1 1 would equal 2. Any help would be greatly appreciated.
b2: {=sumproduct(--(frequency(if(a2:a13=0, row(a2:a13)), if(a2:a13<>0, row(a2:a13))) > 0))}
Entia non sunt multiplicanda sine necessitate
My data is arranged like this, but even when setup like you have yours I cannot get it to work.
Last edited by rosstfer; 10-31-2018 at 05:41 PM.
M4: {=SUMPRODUCT(--(FREQUENCY(IF(A2:L2=0, COLUMN(A2:L2)), IF(A2:L2<>0, COLUMN(A2:L2))) > 0))}
The formula must be confirmed with Ctrl+Shift+Enter rather than just Enter. That's what makes the curly braces appear.
Works perfect! Would it be possible to incorporate if there is a blank cell not to count it?
=sumproduct(--(frequency(if(isnumber(a2:l2), if(a2:l2=0, column(a2:l2))), if(a2:l2=1, column(a2:l2))) > 0))
Thank you so much for your help this is the final tweak I made =IF(SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(H2:K2), IF(H2:K2=0, COLUMN(H2:K2))), IF(H2:K2=1, COLUMN(H2:K2))) > 0))=0, "",SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(H2:K2), IF(H2:K2=0, COLUMN(H2:K2))), IF(H2:K2=1, COLUMN(H2:K2)))>0)))
Or you could use the formula I posted and custom-format as 0;;
Last edited by shg; 11-19-2018 at 02:25 PM.
I have this connected to a pivot table and i think it would "count" everything?
Your final formula calculates twice...
This is better:
=IFERROR(1/(1/formula),"")
where formula = the unduplicated formula you settled with.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
Glenn Im not grasping what your showing me. Sorry its Monday
ok I ran into a problem I reverted back to your formula but when I have a blank cell between a zero it is not counting it as two occurrences. Work perfect if there is a one between them.
=iferror(1/(1/IF(SUMPRODUCT(--(FREQUENCY(IF(ISNUMBER(H2:K2), IF(H2:K2=0, COLUMN(H2:K2))), IF(H2:K2=1, COLUMN(H2:K2))) > 0))=0),"")
will only perform the calculation ONCE.
Ok sorry to be so much trouble but I cannot get it to work so here is a snip of what I have
0.0 represents a unexcused missed day
blank represents a vacation day
1.0 represents a day worked
So in this scenario the formula should return a 2 and not a 1. As any group of missed days count as 1. But if a vacation day is between tow missed days it counts it as 1 when it should be a 2.Capture.PNG
Is it possible to add a number code for a vacation day? If 2.0 represented a vacation day (in cell C2) then the array entered formula (see post #4) could be modified to read:Formula:
Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks