Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenSun, 31 Mar 2013 22:13:57 +0200Finding words and numbers in multiple columnshttps://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/
About a year ago I asked [this question](http://ask.libreoffice.org/en/question/1636/countif-used-finding-words-in-multiple-columns/) with success. Now I'm trying to find when a word and number are in the same row but different columns, such as with "racecar" and "1". SUMPRODUCT doesn't work with numbers.Sun, 31 Mar 2013 08:28:54 +0200https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/Answer by m.a.riosv for <p>About a year ago I asked <a href="http://ask.libreoffice.org/en/question/1636/countif-used-finding-words-in-multiple-columns/">this question</a> with success. Now I'm trying to find when a word and number are in the same row but different columns, such as with "racecar" and "1". SUMPRODUCT doesn't work with numbers.</p>
https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?answer=15490#post-id-15490Hi Levity, in your previous question data were already in different column.
=SUMPRODUCT(H2:H118="racecar";I2:I118="shoe")
=SUMPRODUCT(ISNUMBER(FIND("racecar";H2:H118));ISNUMBER(FIND("shoe";i2:i118)))
In the last versions, the interpretation of numbers as text and vice versa is stricter.
So with the first formula, you can enter the number without quotes, or force the range as text:
=SUMPRODUCT(H2:H118="racecar";I2:I118=1)
=SUMPRODUCT(H2:H118="racecar";I2:I118&""="1")
Sun, 31 Mar 2013 17:01:12 +0200https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?answer=15490#post-id-15490Comment by m.a.riosv for <p>Hi Levity, in your previous question data were already in different column.</p>
<p>=SUMPRODUCT(H2:H118="racecar";I2:I118="shoe") <br>
=SUMPRODUCT(ISNUMBER(FIND("racecar";H2:H118));ISNUMBER(FIND("shoe";i2:i118))) </p>
<p>In the last versions, the interpretation of numbers as text and vice versa is stricter. <br>
So with the first formula, you can enter the number without quotes, or force the range as text:
=SUMPRODUCT(H2:H118="racecar";I2:I118=1) <br>
=SUMPRODUCT(H2:H118="racecar";I2:I118&""="1")</p>
https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15501#post-id-15501Replace the = with >, or with any of the comparison operators. In SUMPRODUCT() you have to do ordinary comparisons (not quoted operators), different than SUMIF() or COUNTIF(), in those the operators are part of the second function parameter.Sun, 31 Mar 2013 22:13:57 +0200https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15501#post-id-15501Comment by Levity for <p>Hi Levity, in your previous question data were already in different column.</p>
<p>=SUMPRODUCT(H2:H118="racecar";I2:I118="shoe") <br>
=SUMPRODUCT(ISNUMBER(FIND("racecar";H2:H118));ISNUMBER(FIND("shoe";i2:i118))) </p>
<p>In the last versions, the interpretation of numbers as text and vice versa is stricter. <br>
So with the first formula, you can enter the number without quotes, or force the range as text:
=SUMPRODUCT(H2:H118="racecar";I2:I118=1) <br>
=SUMPRODUCT(H2:H118="racecar";I2:I118&""="1")</p>
https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15494#post-id-15494What about if I wanted to do ">1" ?Sun, 31 Mar 2013 18:49:14 +0200https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15494#post-id-15494Comment by Levity for <p>Hi Levity, in your previous question data were already in different column.</p>
<p>=SUMPRODUCT(H2:H118="racecar";I2:I118="shoe") <br>
=SUMPRODUCT(ISNUMBER(FIND("racecar";H2:H118));ISNUMBER(FIND("shoe";i2:i118))) </p>
<p>In the last versions, the interpretation of numbers as text and vice versa is stricter. <br>
So with the first formula, you can enter the number without quotes, or force the range as text:
=SUMPRODUCT(H2:H118="racecar";I2:I118=1) <br>
=SUMPRODUCT(H2:H118="racecar";I2:I118&""="1")</p>
https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15493#post-id-15493Doh, I didn't think to try it without the quotation marks. Thanks for responding so quickly!Sun, 31 Mar 2013 18:45:18 +0200https://ask.libreoffice.org/en/question/15461/finding-words-and-numbers-in-multiple-columns/?comment=15493#post-id-15493