How to count values in columns when value is not zero. Stop when 0 and start again when va

**Stijn_NL** · 03-20-2024, 10:42 AM

Hi All,

Some background;
We have produced several articles which took a certain number of hours.
We have the number of hours per week for all three articles, but we want to know the average run length.
When there are production hours in following week, we need to sum these values because it is one run. (only 100 hours per week). Thereby we need to stop taking the sum when there is a 0 and start again when there is a value bigger than 0.
The last part is the most difficult parts which I am not able to count the values with IF statements.

In the attachment you can find in orange the data. The yellow part is the cells in which I want to put the formulas. The green are both possible solutions. For me it doesn't matter if it is solution 1 or 2 because the average run length is the same.
Can you please let me know which formulas are suitable for the cells B10:J12?

Thanks for your help.

Best regards,
Stijn

**AliGW** · 03-20-2024, 10:44 AM

Administrative Note:

Welcome to the forum.

Is your forum profile showing the Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

**Stijn_NL** · 03-20-2024, 10:50 AM

Thanks, I thought it was the version but I have changed it to MS365 right away!

**AliGW** · 03-20-2024, 10:52 AM

It's both - product AND version, please.

From my post above:

... if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306)

**AliGW** · 03-20-2024, 11:10 AM

Your logic does not seem consistent in the second table, but this gets close to what you have suggested as your expected results and may even be more accurate:

=IF(AND(ISNUMBER(A3),A3>0),0,SUM(IF(B3>0,TAKE(B3:$J3,,MATCH(0,B3:$J3,0)-1))))

If it's not right, then EXPLAIN your logic.

**windknife** · 03-20-2024, 11:14 AM

Try this in B10, then copy down and across.

Please Login or Register  to view this content.

**ExceLogan** · 03-20-2024, 03:37 PM

hi everyone, I allow me to propose this alternative solution with one single formula:

Please Login or Register  to view this content.

I hope it results useful.

Question ExcelForum (Alternative Solution).xlsx

**DJunqueira** · 03-20-2024, 08:46 PM

Hi ExceLogan, a bit off topic, but trying to help and clear a question, are you trying to say "permitame" (Allow me) when you use the expression "I allow me"? (Yo me permito)

**AliGW** · 03-21-2024, 01:57 AM

There is no ambiguity here that I can see - ExceLogan's meaning is perfectly clear.

**Stijn_NL** · 03-22-2024, 06:59 AM

Thanks, this was exactly what I needed!

**AliGW** · 03-22-2024, 06:59 AM

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

How to count values in columns when value is not zero. Stop when 0 and start again when va

LinkBack

Thread Tools

Rate This Thread

Display

How to count values in columns when value is not zero. Stop when 0 and start again when va

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Re: How to count values in columns when value is not zero. Stop when 0 and start again whe

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Countif formula with criteria, stop counting if not found, but start count over after

Countif formula with criteria, stop counting if not found, but start count over after

Click Button to start/stop a Stop Watch

How to stop automatically converting text columns to date values

Indicate Duplicate with Small Time Span? (ID#, Start Date, Stop Date columns)

Gantt-like chart but without dates, start and stop values listed instead

Identifying Where Values Start and Stop in a Row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions