# Help with Count Formula

#### gsmith923

##### Board Regular
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!

### Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

#### Mike Blackman

##### Well-known Member
Hi,

Maybe;

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

#### ChompGator

##### Board Regular
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.

Last edited:

#### Mike Blackman

##### Well-known Member
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

##### Board Regular
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.

Replies
2
Views
176
Replies
3
Views
170
Replies
13
Views
507
Replies
1
Views
119
Replies
13
Views
808

1,191,170
Messages
5,985,066
Members
439,938
Latest member
MAlhash

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back