LinkBack URL
About LinkBacks
=SUMIF(A2:A100,"<>#N/A")
Hi guys can someone modify this above formula so in addition to ignoring #N/A cells it
ignores #DIV/0! also and only sums the cells in the range A2:A100 that have values in
them.
Thanks.
=SUMIF(A2:A100,"<>#N/A")
Hi guys can someone modify this above formula so in addition to ignoring #N/A cells it
ignores #DIV/0! also and only sums the cells in the range A2:A100 that have values in
them.
Thanks.
Do you have any negative values? If not try just
=SUMIF(A2:A100,">0")
that will ignore any errors
Audere est facere
Try
=SUM(SUMIF(A:A,{"<0",">0"}))
Wow, thanks for the quick response.
That was a very smart solution.
---------- Post added at 11:11 AM ---------- Previous post was at 11:09 AM ----------
Hi jason.b75,
Can you tell me how your formula =SUM(SUMIF(A:A,{"<0",">0"})) is different from =SUMIF(A2:A100,">0") in
terms of function or speed or accuracy that is?
Thanks.
---------- Post added at 11:17 AM ---------- Previous post was at 11:11 AM ----------
jason.b75,
I tried your formula, but it dint work well with my data.
Thanks for the help though.
In terms of speed, it will take longer as it sums against 2 criteria instead of 1, then adds them together, however, given your data volume, it should still be an instant calculation.
In terms of function / accuracy, it will sum negative values as well as positive.
If you have values that should be ignored below A100 then you would need to change the formula range to ignore those.
=SUM(SUMIF(A2:A200,{"<0",">0"}))
But if not then it shouldn't be necessary.
Could you elaborate on 'didn't work well', what happened that shouldn't?
Last edited by jason.b75; 08-22-2012 at 12:21 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks