Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

1. Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.

2. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Intalzky

Assuming your list starts in A2, then in any cell on row 2 type
``Please Login or Register  to view this content.``
and copy down as far as necessary.

Let me know if this is OK for you

Regards
Alastair

3. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi aydeegee, thank you for your Quick response. To give you a better understanding regarding my problem please refer below.

Capture.PNG

Column A would be the Names of Agents.
Column B:1 to K:1 would be number of days.
And below the days are their percentage. What I would like to do is to count the number of consecutive "0%" in Column M.
However, here is the catch. for example Agent 1 already have 4 consecutive "0%" from Day 1 to Day 6, but on Day 7 Agent 1 incurred 5%. the count will be reset and start again to count starting from the next "0%" Day. (Day 8 on the example). So Agent 1 have 3 days consecutive "0%".

And just to add, I would like also to exclude the blank cells in the count. Because blank cells means that agents are OFF from work during that day.

I really hope you can help me with this.

Thank you.

4. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Intalzky

I am unable to get access to Excel at present - serious hardware issues.

I will get back to you as soon as I can, but anyone else should feel free to assist.

Regards
Alastair

5. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Intalzky

Here a solution. It uses a macro - so if you have not used macros before, you may have to adjust your Excel security level.

Click on the blue button to run the macro. To see the macro press Alt+F8 / Step Into

Regards
Alastair

6. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Alastair, This perfectly works. However using macro is the last that I can think of because this is a running file. Number of days will be added as the days go by and so will for the number of agents. What I'm really preferring is an excel formula to use.

But if macro is the only way to do this. Is there as way to adjust the columns and rows if I'll be adding data? Can you please teach me as I'm not that good in terms of VB codes.

Thank you so much

7. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Intalzky

The macro already worked for as many rows as necessary, it now caters for as many columns as necessary.

It may be possible to do it with a formula, but I am not clever enough to work it out.

I have annotated the macro, so that you may better understand what is going on. Please note that I am not claiming that this is good programming, or anything - just that it works

Regards
Alastair

8. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Alastair. This is awesome!!! Thank you so much Last Favor, is it possible to set a conditional format on the answers? I want to highlight in green the cell with Answer equal to 45 and above.

9. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

=IF(ISNA(LOOKUP(2,1/(B2:K2<>0))),COUNT(B2:K2),COUNTIF(INDEX(2:2,LOOKUP(2,1/(B2:K2<>0),COLUMN(A2:J2))+2):K2,0)) gives those results

10. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Martin - Nice one!

Intalzky

Martin's solution is what you originally asked for, but in case you still want the conditional formatting, here it is.

Regards
Alastair

11. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Same result with frequency:

=IFERROR(INDEX(FREQUENCY(IF(B2:K2="","",COLUMN(B2:K2)-COLUMN(B2)+1),LOOKUP(2,1/(B2:K2<>0),COLUMN(B2:K2)-COLUMN(B2)+1)),2),COUNT(B2:K2))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

12. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Thank you everyone! You're all awesome!

Alastair's Macro and Martin's formula works perfectly..

Hi bebo,

Thanks for your response but I think something is missing in the formula. as it counts the all % including non zero cells

13. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Alastair,

I came up with a new scenario using the template with macro. I really hope you can help me with this.

When the count already reaches 45, can the count be reset back to zero?
For example, Agent 1 already have 45 consecutive 0% can the count be reset starting from the next day Agent 1 have 0% again and be reset again for the next time Agent 1 reaches another 45 consecutive 0% again and so on.

And if possible, on the next sheet. I wanted to see the Dates where Agents reaches the 45 count.

Example:

1st 2nd 3rd
Agent 1 March 1 April 15 May 30 and so on.

Thank you so much in advance!

14. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

to count Zeros you could also use
copy paste the formula then hold control and shift together and hit enter to make it array formula

=SUM(--((COLUMN(B2:K2)>MAX(IF(B2:K2>0,COLUMN(B2:K2),0)))*(B2:K2<>"")))

15. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Hemesh,

Your formula for counting consecutive zeros is working. however, i just came up with a new scenario that if the count reaches 45, the count will be reset back to zero by the next day.

And also if there's a formula to get the DATE where the agent got the 45th consecutive 0.00%

I really hope you can help me with this.

16. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

can you please upload a sample showing what you are trying to achieve. I think you will need a helper column.

17. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Hemesh,

I have attached a sample file for your reference. Raw Data is in Sheet 1 and formula that I need to work is in Sheet 2.

Thank you so much in advance!

18. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hello Intalzky,
This formula will work till data in HQ column if data range expands then change references in the formula about which I have written in attached book
try below in D2 in Sheet 2
Formula:
`Please Login or Register  to view this content.`

above formula is array entered

drag down and drag to right

Hope this helps

19. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Hi Hemesh,

Thank you for checking on this. The formula almost work but I think its still missing something.

For example on Agent 107, this agent incurred 45 consecutive 0.00% on 04/11/14. but the formula is showing blank.

20. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

Originally Posted by Intalzky
Hi, If you can help me with a formula to count the number of consecutive zero starting from the last cell with non zero value.

for Agent 107 the consecutive 0 after non zero value is only 3 so formula is showing blank. If you make Cell BU107 to 0 instead of 3.33 then you will get your answer

21. Re: Count Consecutive 0 starting from the Last non zero cell in a row (excluding blanks)

That is correct, that is why the formula on column B should be revised. Because still Agent 107 incurred a 45 consecutive 0.

If we can have the formula count the 45 consecutive 0, then start counting again on the next day with 0 value.

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1