Hello,
Is it possible to skip cells that contains #N/A in table and count only sum of valid numbers ?
Hello,
Is it possible to skip cells that contains #N/A in table and count only sum of valid numbers ?
Last edited by saligatvis; 05-06-2019 at 06:10 AM.
With a macro, but I would make sure to avoid #N/A at all times and if you correct your formulas, that would be no problem using the ISNA function or IFERROR, in all those cases the result can be 0 and will not influence the adding and or counting.
---
Hans
"IT" Always crosses your path!
May the (vba) code be with you... if it isn't; start debugging!
If you like my answer, Click the * below to say thank-you
So currently i am ussing formula =SUBTOTAL(9;BT2:BT220) how could i add iferror ?
Formula:Please Login or Register to view this content.
However, I think, you have to deal with error (#N/A) in particular cells in BT2:BT220. If you have formulas there, use IFERROR like above.
If you get data from other source as value, and #N/A can appear, imho instead of SUBTOTAL, you can use:
accept with Ctrl+Shift+Enter.Please Login or Register to view this content.
Last edited by KOKOSEK; 05-06-2019 at 06:51 AM.
Happy with my answer * Add Reputation.
If You are happy with solution, please use Thread tools and mark thread as SOLVED.
No still doesn't work with both of them he misses eror cells and all others
You must check that none of the cells on the range shows #N/A
That’s troubleshooting, patience and perseverance
Without any file and sample of hwt YOU have and are doing, help ends here, it's a question of making sure that in none of the cells the error #N/A is not there, else no go
I tried couple of versions added file to attachment
If you have XL2010 or above =AGGREGATE(9,6,H13:H25)
What you need to do is make sure you do NOT van invalid values in the table, if the formula fills this returns #N/A make the value 0 and hide 0 values in the cell formatting
The aggregate function of course is better but... the OP has 2007
@saligatvis - You have put my formula but you did not accept it with CSE.
Put this:
Formula:Please Login or Register to view this content.
and accept with CSE then will be fine.
That's from you attached file:
Capture.JPG
Wha does it mean accept with CSE?
Attachment 623254
CSE means that it's an array formula.
Array Formulae are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.
You will know the array is active when you see curly brackets { } - or "curly braces" for those of you in the USA, or "flower brackets" for those of you in India - appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.
Don't type the curly brackets yourself - it won't work...
However, this will also work and works with just Enter:
=SUMIF(H13:H25,">-1000000")
Change the -1000000 if you need to.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
When i accept with CSE it works but it counts even hidden rows which i am trying to avoid
I am lost now. Do you want to count or sum.
In your post:
"So currently i am ussing formula =SUBTOTAL(9;BT2:BT220) how could i add iferror ?"
I get that you want to SUM range, right? But if you got error in range you have got error in this sum.
Formula:Please Login or Register to view this content.
accepted with Ctrl+Shift+Enter, gives you SUM in range even if you have got errors in particular cells (0 value is assign for them).
What do you mean as hidden rows?
Post a sample sheet that shows EXACTLY what you want. You have suddenly mentioned hidden rows. There were none in your attachment. We cannot guess what is in your head. Show us on a sample sheet.
Also, are these actual #N/A errors... or are they just typed in? In your sample they are just typed in.
Last edited by Glenn Kennedy; 05-08-2019 at 06:24 AM.
I was just going to say that avoid others having to think what you want. explain it clear and concisely, YOU understand it but don't expect other to do so with an incomplete explanation and no knowledge of the actual data
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks