| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Subtotal counting formula in cells
I have a worksheet that is using the subtotal function. Some of the columns
contain formula. In the columns containing formula when no filter is selected the subtotal counts the data and also the cells containing the formula ie =subtotal(3,B2:B50) shows a subtotal of 49 yet there is only text in 10 of the rows. Is there a way for the subtotal to count only those cells containing data and to ignore the cells containing just the formula? |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Subtotal counting formula in cells
If you mean that the range of cells contains text values, including
formula blanks "", try... =SUMPRODUCT(SUBTOTAL(3,OFFSET(B2:B50,ROW(B2:B50)-ROW(B2),0,1)),--(B2:B50< "")) Hope this helps! In article , jimar wrote: I have a worksheet that is using the subtotal function. Some of the columns contain formula. In the columns containing formula when no filter is selected the subtotal counts the data and also the cells containing the formula ie =subtotal(3,B2:B50) shows a subtotal of 49 yet there is only text in 10 of the rows. Is there a way for the subtotal to count only those cells containing data and to ignore the cells containing just the formula? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formula for counting number of cells | Excel Discussion (Misc queries) | |||
| subtotal a range of cells on a different worksheet | Excel Worksheet Functions | |||
| Conditional formatting...cont. from 9/25 | Excel Discussion (Misc queries) | |||
| Formula for counting specific cells with value greater than 0 | Excel Discussion (Misc queries) | |||
| Formula for counting specific cells which are greater than 1 | Excel Discussion (Misc queries) | |||
Linear Mode
