LinkBack URL
About LinkBacks
Respected Excel Gurus,
I have a paragraph which contains values. I want the sum of that values.
Attaching herewith Excel File for reference.
Thanking you.
Regards!
SGK
Respected Excel Gurus,
I have a paragraph which contains values. I want the sum of that values.
Attaching herewith Excel File for reference.
Thanking you.
Regards!
SGK
Please try
at F9
=FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C$3,"worth ","+"),"spent ","-"),CHAR(10)," ")," ","</m><m>")&"</m></x>","//m[contains(., '$')]["&ROWS(F$9:F9)&"]")
F16
=SUMPRODUCT(FILTERXML("<x><m>"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C$3,"worth ","+"),"spent ","-"),CHAR(10)," ")," ","</m><m>")&"</m></x>","//m[contains(., '$')]"))
I differentiate Received and spent by "worth" and "spent".
Bob got 50 reward points worth $100
he bought books worth $37
$37 also with received.
< deleted >
Last edited by Mgc26133; 01-08-2021 at 02:12 PM.
a UDF variant, stored in standard VBE module
could be called as
Please Login or Register to view this content.
=SUM_STRING(C3)
or, if you needed to customise you could pass parameters explicitly, e.g.:
=SUM_STRING(C3,"$","BOUGHT|SPENT")
where:
the $ denotes the monetary delimiter
the pipe delimited string would store all terms that represent Expense items and precede the monetary delimiter
by default, both of the above variants would return the same answer, 75.43, however, if you changed BOUGHT|SPENT to just BOUGHT you would get 150.57 as the SPENT items would then be considered income
Got the formula from XLent's Logic
Formula:
Please Login or Register to view this content.
Last edited by Bo_Ry; 01-07-2021 at 11:20 AM.
Bo_Ry. A lovely piece of work. I am trying to learn FILTERXML... having failed a month or so ago. Both FILTERXML and Bo_Ry are amazingly versatile... Amazingly, I can at least now understand the formula. Whether or not I could reproduce it is another matter entirely.
Your first solutions on this thread seemed to me to be a bit unstable. It didn't take much intervention to cause the . to appear after the 37 and the 15 and for the values to be returned as left-justified text. This one seems perfect.
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
@ XLent
No Doubt ... Truly EXCELLENT ...!!!
@Glenn, Thanks for the rep and very kind comment.
There are many more XPath syntaxes that I don't understand like " following::, preceding::" that I tried to apply but failed.
Hopefully, that some XML expert will step in and show me how to use efficiently XPath
Please check this link for more XPath.
https://docs.oracle.com/cd/E35413_01..._functions.htm
Another UDF
=sumst(C3)
Please Login or Register to view this content.
Bo_Ry Sir,
Thanking you very much. Genius Work done by you Sir.
Thanx once again.
Regards!
SGK
Mgc26133 Sir,
Doing the calculation manually in this case is much faster. But what if I have 4 to 5 A4 Size pages which contains values like this.
Anyway. Thanx very much for your reply.
Regards!
SGK
XLent Sir,
Thanking you very much for reply.
Regards!
SGK
Bo_Ry Sir,
You people are truly, indeed mastermind. Don't have the words. Thanking you very much.
Regards and Happy New Year to all of you.
SGK
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks