Help with Count Formula

[gsmith923]

I have a formula that sums several weeks of sales. Based on that formula, I want another formula that will give me a count of cells used to create the sum total. I know I can use the count function, but I would have to manually update the formula each time the sum formula is updated.

So, if I have a formula like this: =SUM(H56:H57)+SUM(E6:E55), what corresponding formula can I use that will count the total rows without having to update the count formula when the sum formula is updated?

Thanks!

 

[Mike Blackman]

Hi,

Maybe;

=COUNTIF(H56:H57,">0")+COUNTIF(E6:E55,">0")

 

[ChompGator]

Or wouldn't something like

=COUNT(A2:A10000)

work?
For just counting how many cells contain data.

Or even
DCOUNT(Sheet1,A2:A10,">0")

Now Im not sure if that DCount will work, it has not been tested, I just came across that function myself a few days back, but Im sure Mike might be able to provide some insight or help...

I have not tested his but try his first before mine, his solutions seem to work well.

 

[Mike Blackman]

I think now I mis-understood, but my thinking was that the Op was only after the values that made up the SUM. COUNT would include any formula that returns a 0 where as my COUNTIF would only return values of more than zero.

 

[gsmith923]

1st of all, thanks for your input but let me explain further. I have year over year data and am compiling new data to measure against old data, meaning that some sales will be blank, while older data will have historical sales. The count function will work on the blank cells that are updated each 4 or 5 weeks, but the historical data will be sitting there until each week cycles over the previous year.

I was thinking of using a "Replace" formula to somehow replace the sum portion of the formula with count in another cell. My thinking was when I updated the sum formula cell references, the "Replace" formula in another cell would do the same.