Count if or sum if

[neb]

My formula should reside in cell B6. B6:IV6 will contain either a 1 or a zero. I need to count consecutive ones and add them till they total 26. If a zero occurs, start the count again from that point on. I'm stumped. I can count and add all the information and come up with a total of 26, but this includes the cells containing zeros, which is not what I want. Any help would be appreciated.

[Domenic]

Can you provide a sample of your data and expected results?

[neb]

I need to total the ones across until I encounter a zero. If a zero in encountered, the count starts again in the next cell. The count stops when 26 consecutive ones are encountered.

Thanks so much for taking a look.

Jan

[Domenic]

It's still not too clear to me as to what you're looking for. Can you enter some sample data (a few rows) and provide the actual results that you'd like to see?

[neb]

Hope this is clearer.

Thanks for your perseverance!

Jan

[neb]

I found a way to do this by arranging the sheet differently and using subtotals. Thanks for trying.

Jan

[neb]

This worksfor only one column at a time. Still need a solution. If it would be easier to do vertically than horizontally, I can adjust!

[Domenic]

Try...

B6, copied down:

=IF(ISNUMBER(MATCH(TRUE,SUBTOTAL(9,OFFSET(B6,0,ROW(INDIRECT("1:230")),1,26))=26,0)),26,0)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER. The formula will return '26' once 26 is reached, otherwise '0' will be returned. Will this do?

[neb]

It gives me an error message: See attached. Thanks again for helping! This has me stumped.

[Domenic]

That's becuase you're missing a bracket in the formula. Correct this and make sure you confirm the formula with CONTROL+SHIFT+ENTER and you should be okay.

[neb]

Am on the run right now, but will do it again later this evening.

Will write of my success later (hopefully!)

THANK YOU!

Jan

[Domenic]

Correction...

=IF(ISNUMBER(MATCH(TRUE,SUBTOTAL(9,OFFSET(B6,0,ROW(INDIRECT("1:229")),1,26))=26,0)),26,0)

...confirmed with CONTROL+SHIFT+ENTER.

[neb]

Well, here I am back again, but with bad news. I corrected the missing bracket and entered it as directed as an array. This time it was accepted; however, it doesn't calculate for me. I even tried doing a manual calculation. Then, I thought it might be because I am using a mac, so I redid it on my Dell, and got the same results. Any thoughts as to why?

I attached a copy FYI.

Thanks again.

[Domenic]

I posted a correction to my formula. In case you missed it, here it is...

=IF(ISNUMBER(MATCH(TRUE,SUBTOTAL(9,OFFSET(B6,0,ROW (INDIRECT("1:229")),1,26))=26,0)),26,0)

Does this help?

[Domenic]

Also, I just noticed that the formula you're entering in your spreadsheet contains additional spaces. Probably brought about by copying the formula from the post and pasting it into your spreadsheet. Correct this and the formula should work.

Does this help?

[neb]

Finally got it. I didn't realize it does not do a running count as one enters data. But, this will do nicely. Thanks for all your help. The personnel people will be thrilled when I finish up the sheet for them.


Gratefully,

Jan

[Domenic]

First, I just notice that the actual pay periods start in Column D. In this case, change my previous formula as follows...

=IF(ISNUMBER(MATCH(TRUE,SUBTOTAL(9,OFFSET(C6,0,ROW(INDIRECT("1:228")),1,26))=26,0)),26,0)

Secondly, for a running count, try the following formula instead...

=SUM(INDEX(D6:IV6,IF(COUNTIF(D6:IV6,0),MATCH(2,1/(ISNUMBER(D6:IV6)*(D6:IV6=0))),0)+1):IV6)

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

[neb]

Everything is working fine now.

Thanks so much. I really appreciate your getting me over this obstacle!

Jan

[mm22]

Hi Domenic or anyone else who can help

I want to be able to simply create a running balance. I have one cell in which I put the dollar amount for all of my withdraws (I will simply input a new amount over the previous one so I'm only using one cell for the withdraws) and then the other cell I want to create a running balance which will continuously add the withdraws.

Here's how I want it to run. I make a withdraw of $10.00 which I input under the "withdraw" header cell. Now I have a running total of $10.00 in under my "total withdraws" cell. A few days later I have a withdraw of $5.00. So I mouse over the cell that I typed $10.00 (under the "withdraw cell") and type in $5.00. Now the "total withdraws" cell has a running balance of $15.00

Thank you for your time!!

Mike

[mm22]

I forgot to say that I want to know what the formula would be for the "total withdraws" cell? I've played around with different formulas and operators and I can't get it to work.

Mike

[neb]

Thanks so much for your help. I did some conditional formatting and prettying up the sheet. It works fabulously and was exactly what was needed. I have enclosed a picture of the final product for your viewing pleasure!

I am very appreciative of your help. Thanks ..... Jan