| View previous topic :: View next topic |
| 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. |
|
| Back to top |
|
 |
karolus
OOo Advocate
Joined: 22 Jun 2011
Posts: 208
|
| Posted: Mon Feb 13, 2012 4:42 am Post subject: |
|
|
Hi
=SUMPRODUCT(A1:A4=219270001;(ISNUMBER(SEARCH("^96.*";B1:B4)));C1:C4)
Karo |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
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? |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
|
