hi
My data set looks as follows starting from Cell A1 going down
1
1
2
3
d1
h22
x
1%
10.00%
5%
50
I would like a subtotal in the last cell which would total 96
i cannot use a helper colum it needs to be in one column
it should ignore the percentage symbol and other text
SUM function already ignores text.
Problem you've probably got is that "5%" probably isn't a text string with the "%" character on the end, it's the number 0.05 formatted as a percentage.
Here's my first approximation of an answer:
This sums up all numbers in range A1:A100 that are at least 1. So 1, 500, or 3.14 would all be added up, but for example, 50% would be ignored (because 50% = 0.5).
Please Login or Register to view this content.
Note this means if you have something like 300% that will be included (because 300% = 3) and conversely per above if you have 0.5 you want included but 50% you don't in the same range, this wouldn't be able to tell the difference. And telling the difference is actually pretty hard; that's because excel functions aren't designed to care about the formatting, like % vs. decimal number or whatever.
Last edited by ben_hensel; 06-08-2020 at 08:37 PM.
Click the [* Add Reputation] Button to thank people for help!
Post Attachment: Click [Go Advanced] under the reply box; then click [Manage Attachments] to launch the attachments dialogue.
Mark Thread Solved: Go to the top of the first post; Select [Thread Tools] dropdown; Pick the [Mark thread as Solved] option.
hi
how about the d1 or h22 etc..
Last edited by AliGW; 06-09-2020 at 01:47 AM. Reason: Please don't quote unnecessarily!
i also can't have it ignore any number it shoulld sum all numbers including if there is a percantage symbol or not
Last edited by AliGW; 06-09-2020 at 01:47 AM. Reason: Please don't quote unnecessarily!
are... are these supposed to be references to other cells entered as text?Originally Posted by BORUCH
Yikes that's foul
Anyway you're going to have to create a helper cell and run an IF to test that with INDIRECT to see if text cells contain a valid cell or range reference.
I don't think you can embed that in an array formula construction like {SUM(IF)} because that won't cooperate with a volatile function like INDIRECT.
a helper colum will not work is there a vba or udf that can do the job ?
no its no reference at all
Last edited by AliGW; 06-09-2020 at 01:48 AM.
in that case why can't you just do a straight SUM(A1:A100) on it? Why doesn't that work?
because it won't sum the 1 after the d or the 22 after the h and it will not sum the 1% etc..
Give this UDF a try...
Simply give it the range to evaluate as its argument. For example...Please Login or Register to view this content.
Formula:
Please Login or Register to view this content.
perfect! that worked thanks alot
just out of curiosty can this udf be repliacted in a regular excel formula using excel standard functions ?
Last edited by AliGW; 06-09-2020 at 01:48 AM. Reason: Please don't quote unnecessarily!
No it can't, at least not as far as I know.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks