Help with Count Formula

gsmith923

Board Regular
Joined
Jul 31, 2007
Messages
50
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

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,322
Members
448,564
Latest member
ED38

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top