[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
drtduarte
Newbie

Joined: 15 Oct 2009
Posts: 4

 Posted: Mon Feb 13, 2012 3:55 am    Post subject: SUMPRODUCT + ISNUMBER + SEARCH + WILCARD STARTING BY Dear Friends, After some time searching on forums I found the functions for what I'm trying to do, however I still need to fix a litle detail. Formula I'm using: =SUMPRODUCT(A1:A4=219270001;(ISNUMBER(SEARCH("96.*";B1:B4)));C1:C4) A________ --- B________ --- C 219270001 --- 961222444 --- 120 219270001 --- 934459610 --- 100 219270002 --- 961222444 --- 130 219270001 --- 916231444 --- 4 The problem is that "96.*" or "96", returns also C on 934459610, if I use "96*" it is even worse as it returns also C on 916231444. Do you know how to return only when started by 96? Regards, David D.
karolus

Joined: 22 Jun 2011
Posts: 210

 Posted: Mon Feb 13, 2012 4:42 am    Post subject: Hi =SUMPRODUCT(A1:A4=219270001;(ISNUMBER(SEARCH("^96.*";B1:B4)));C1:C4) Karo
gerard24
OOo Enthusiast

Joined: 08 Jul 2011
Posts: 100
Location: France

Posted: Mon Feb 13, 2012 4:59 am    Post subject:

.* is useless.

 Code: =SUMPRODUCT(A1:A4=219270001;(ISNUMBER(SEARCH("^96";B1:B4)));C1:C4)

_________________
LibreOffice 3.5.0 on Windows Vista
drtduarte
Newbie

Joined: 15 Oct 2009
Posts: 4

 Posted: Tue Feb 14, 2012 12:33 am    Post subject: Hello there, Thanks a lot for your replies. However I tried both solutions and none of them work, I get 0 results with both, did you test them?
drtduarte
Newbie

Joined: 15 Oct 2009
Posts: 4

 Posted: Tue Feb 14, 2012 12:39 am    Post subject: Hi again, It is working, sorry for my prior reply, the problem was that I needed to activate again the use of regular expressions, as I'm using a xls doc, each time I open the document I need to activate it... A huge thank you for your help, I would never find out the ^ use in formulas. Take care, David
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum