Dynamic range for a subtotals of rows

[doubl3d80]

I am looking for a dynamic formula that I would be able to plug into a cell and count down until the next blank is hit. Sounds easy but the number of rows can change everyday. It would be nice to be able to have something to where I copy the rows that have a date in them and paste them. They would then count the rows with data until a blank. I have provided an example if this is confusing. Sheet 1 has what my data looks like and Sheet 2 is what I would want it to look like counting the data but would of course what that number to be a formula that does it automatically.

[doubl3d80]

Looking to count the number of rows with data which would just be CountA. I want to be able to paste this on each row for a given date and it count until there is another blank or it could count until it hits a different date. Attached is a document that contains the data similar to what I have on Sheet 1 and then Sheet 2 what I would want to have with the subtotal numbers being the formula.

[icestationzbra]

not a pretty solution, but here is what i could come up with...

in your sample workbook, paste this formula FIRST in cell B3, and ARRAY-confirm it:

Please Login or Register  to view this content.

once you have entered and confirmed that formula in B3, THEN copy it and paste it on to B6, B9, B15 and B18.

UPDATE:

in case your data span farther than A27, just replace with a high number, such as A100.

[FDibbins]

How is this thread any different to this 1?

http://www.excelforum.com/excel-form...subtotals.html

[kvsrinivasamurthy]

PL see the attached file with formula.

[doubl3d80]

Yes this is a similar thread, I assumed this was poorly worded so I thought I would try again. Kvsrinivasamurthy thanks for the formula but it seems like if more data is added to the end of the 27th row then it will not be calculated correctly. The formula is basing itself off that row so when its changed then the data is messed up.

[doubl3d80]

Works great but once there are new rows added then it won't count anything passed the 27th row. If the 27 in the formula is extended then the total is counted and then it subtracts the rows.

[kvsrinivasamurthy]

If more data is added in the formula you have to change 27 by the last row number or use the below formula so that up to 1000 rows you need no change.

It is array formula. After editing Press Ctrl+Shift+Enter keys simultaneously.

Please Login or Register  to view this content.