Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
Hi all.
I wish to use the SUBTOTAL function, to count the number of rows when using 'Autofilter' I have this formula: =SUBTOTAL(3,B5:B300) The problem is that all cells in the range B5:B300 contain formulae, and is giving the value 296. Is there any way to edit the formula, to count only values, in B5:B300? The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5) this is copied down to B300, so the values in B5:B300 are either a blank cell or "a" Is this clear? Many thanks George Gee -- |
#2
![]() |
|||
|
|||
![]()
One way
=SUMPRODUCT(--($B$5:$B$300<""),--(SUBTOTAL(3,OFFSET($B$5,ROW($B$5:$B$300)-MIN(ROW($B$5:$B$300)),,)))) Regards, Peo Sjoblom "George Gee" wrote: Hi all. I wish to use the SUBTOTAL function, to count the number of rows when using 'Autofilter' I have this formula: =SUBTOTAL(3,B5:B300) The problem is that all cells in the range B5:B300 contain formulae, and is giving the value 296. Is there any way to edit the formula, to count only values, in B5:B300? The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5) this is copied down to B300, so the values in B5:B300 are either a blank cell or "a" Is this clear? Many thanks George Gee -- |
#3
![]() |
|||
|
|||
![]()
Peo
Full marks to you! For understanding what I wanted, and supplying the answer. Many, many thanks! George Gee *Peo Sjoblom* has posted this message: One way =SUMPRODUCT(--($B$5:$B$300<""),--(SUBTOTAL(3,OFFSET($B$5,ROW($B$5:$B$300)-M IN(ROW($B$5:$B$300)),,)))) Regards, Peo Sjoblom "George Gee" wrote: Hi all. I wish to use the SUBTOTAL function, to count the number of rows when using 'Autofilter' I have this formula: =SUBTOTAL(3,B5:B300) The problem is that all cells in the range B5:B300 contain formulae, and is giving the value 296. Is there any way to edit the formula, to count only values, in B5:B300? The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5) this is copied down to B300, so the values in B5:B300 are either a blank cell or "a" Is this clear? Many thanks George Gee -- |
#4
![]() |
|||
|
|||
![]()
My Pleasure, thanks for the feedback
Peo "George Gee" wrote: Peo Full marks to you! For understanding what I wanted, and supplying the answer. Many, many thanks! George Gee *Peo Sjoblom* has posted this message: One way =SUMPRODUCT(--($B$5:$B$300<""),--(SUBTOTAL(3,OFFSET($B$5,ROW($B$5:$B$300)-M IN(ROW($B$5:$B$300)),,)))) Regards, Peo Sjoblom "George Gee" wrote: Hi all. I wish to use the SUBTOTAL function, to count the number of rows when using 'Autofilter' I have this formula: =SUBTOTAL(3,B5:B300) The problem is that all cells in the range B5:B300 contain formulae, and is giving the value 296. Is there any way to edit the formula, to count only values, in B5:B300? The formula in B5 is: =IF('Tick sheet'!G5="","",'Tick sheet'!G5) this is copied down to B300, so the values in B5:B300 are either a blank cell or "a" Is this clear? Many thanks George Gee -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Averaging function | Excel Discussion (Misc queries) | |||
Function in XL or in VBA for XL that pulls numeric digits from a t | Excel Discussion (Misc queries) | |||
Subtotal And Countif | Excel Discussion (Misc queries) | |||
Excel function help facilities | Excel Discussion (Misc queries) | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) |