LinkBack URL
About LinkBacks
Hi there,
I need a formula to count filtered rows with data, i am using formula =SUBTOTAL(103,x:x) but do not want the N/A results to show
any help greatly appreciated!
Hi there,
I need a formula to count filtered rows with data, i am using formula =SUBTOTAL(103,x:x) but do not want the N/A results to show
any help greatly appreciated!
Last edited by Gall47; 09-25-2020 at 04:36 AM.
Administrative note
Welcome to the forum
in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?
Please take a moment to read it and attach a sheet accordingly.
Thanks you for helping us help you
Hi
Try the function 3 instead 103.
=SUBTOTAL(3,X:X)
Thanks but this is still counting N/A Cells (which is the result of anthe formula being run)
Hi
Try this
IF you use N/A as text
Formula:
Please Login or Register to view this content.
If you use #N/A as error
Formula:Please Login or Register to view this content.
Thank you it is #N/A as error
When I use the above formula and then filter eg. on name, it is subtracting all the N/S from the subtotal count, not just the filtered range
I am still trying to resolve this and I have attached a better example
I am trying to count column D that has test scores - I just want to know how many people sat an exam when filtered on another field (eg colour in my example).
Some of the scores in D are taken from a formula, and some manual entered
I do not want to include N/A errors in count
I cannot get this to work when filtering data - can anyone help?
I have tried
=SUBTOTAL(3,X:X)-COUNTIF(X:X,NA())
=SUM(IF(ISERROR(x:x),1))
=SUM(COUNTIF(x:x,{">0",""}))
Try =AGGREGATE(3,7,D2:D11)
Last edited by Pepe Le Mokko; 09-28-2020 at 06:28 AM.
Thank you!!!! =AGGREGATE(3, 7, D2:D11) works :-)
I've encountered something that I overlooked previously
Some of the courses have not be completed and have a formula result of "-" I do not want to include these in my count, how to I disregard them?
I tried to Countif(D2:D11,"-") with the aim to minus this from the total, but this isn't working when I filter the data
Last edited by Gall47; 09-28-2020 at 06:26 AM.
Administrative Note:
Welcome to the forum.
We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.
Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.
(Note: this requirement is not optional. No help to be offered until the link is provided.)
Apologies, I was not aware. Understand why you have rule now
Last edited by Gall47; 09-28-2020 at 06:36 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks