Hi
Is there a way to format cells containing numbers so that they will be excluded from a SUM (or any other formula), in other words, render their value zero.
I tried format as text, it did not work, it just left aligned the number.
excel 2002
thanks
Not tested, but have you tried preceding them with a single quotation mark (')?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
NB: as a Moderator, I never accept friendship requests.
Forum Rules (updated August 2023): please read them here.
Yes that would do it, however, that requires editing each one of the cells separately.
I am looking to pick them all, or pick a range and do it.
Find and replace?
Find what?
The values are all different...
Then I don't know - sorry. It wasn't clear from your opening post that you needed to do this en masse.
Never mind... Thanks anyway!
You're welcome! I think the only way is editing each cell concerned.
Maybe another GURU will have a "mass" idea!...
What about a macro to run on several chosen cells (or a range of cells) and put a single quotation mark before the number?
Of course a macro would be required to do just the opposite: remove it...
That might work, but even a macro will need to identify the cells - you say that there is no way that can be done (all different values). How many cells are we talking about?
I can mouse pick the cells, or mouse pick an entire range.
If you set to now and did it manually, would it be likely to be done by the time you got a better answer here? If the answer is "possibly", then I would just get on and do it manually. Either that, or think of a way of reorganising your data so that the cells in question are taken out of the range where the sum is taking place. You haven't said WHY you need those cells to be excluded or WHAT the purpose is. With more information, forum members may be able to offer a better solution.
You could also post a sample workbook here.
You can try a way.... follow below step
Assuming your data is in A column, then select the whole B column->>>> In B1 Cell put =CONCATENATE("",F3) and press control enter. By this way all value will convert in text format and no one function will calculate their value.
Hope it will help you
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
I am doing that now but is awkward. The makros would be perfect but I do not know how to write them.... :-(
The answer is here: https://support.microsoft.com/en-us/kb/192891
Copying just in case the link goes dead:
Please Login or Register to view this content.
Glad your issue is resolved!
If the cells to be excluded are in a contiguous range you could use something like an X to mark them:
Data Range
=SUMIF(B1:B10,"",A1:A10)
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks