Re: [libreoffice-users] Find string within a range of cells

I haven't been reading this thread in-depth, but I think the formula
you're looking for is =SUMIF

Read the help manual documentation for it.

On 2/7/25 21:18, Brian Barker wrote:
> At 15:42 07/02/2025 +0000, Wade Smart wrote:
> > What I have unsuccessfully been trying to do is. SUM(CD6:CD15) + If
> > within this range C6:C13 the string "Upper Swing Pin" is found,
> > reference BG6:BG13 on whatever line that string was found and find
> > the text "lb:" and grab the number to the right * BZ that row number
> > and .. add back to the sum, which is BN17
> >
> > I have had problems from the start. In my sample worksheet C7 = Upper
> > Swing Pin 272s 725w When I use Search or Find "Upper Swing Pin" it is
> > not found.
> >
> > I was testing with this:
> > =IF(ISERROR(SEARCH("Upper Swing Pin", C6:C13)), "Not Found", "Found")
> > =IF(ISERROR(SEARCH("Upper", C6:C13)), "Not Found", "Found")
> > both said not found.
>
> The SEARCH() function will not handle a cell range; instead it seems
> to operate only on the cell in your range that happens to be on the
> same row. If so, and if your result cell is on row 17 but your range
> is from rows 6 to 15, you will indeed never locate anything.
>
> But in any case the SEARCH() function is no help here, as it will
> simply tell you whether the text sting was found, whereas you need
> instead to identify in which row it was found. To do this you need the
> VLOOKUP() function. If, as you explain, your cell content contains
> more than the test text, you will need to use a regular expression and
> search for something like ".*Upper Swing Pin.*" instead. Note that you
> will need to ensure that you have enabled regular expressions in
> formulae in Options.
>
> Once you have harvested the content of the relevant cell in a
> different column of the same row in this way, you'll need to do more
> work to extract the part that you need and will use in further
> calculation.
>
> I trust this helps.
>
> Brian Barker

--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy