Ask Your Question
0

Finding words and numbers in multiple columns [closed]

asked 2013-03-31 08:28:54 +0200

Levity gravatar image

About a year ago I asked this question 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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-10-27 20:35:19.740253

1 Answer

Sort by » oldest newest most voted
1

answered 2013-03-31 17:01:12 +0200

m.a.riosv gravatar image

Hi 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")

edit flag offensive delete link more

Comments

Doh, I didn't think to try it without the quotation marks. Thanks for responding so quickly!

Levity gravatar imageLevity ( 2013-03-31 18:45:18 +0200 )edit

What about if I wanted to do ">1" ?

Levity gravatar imageLevity ( 2013-03-31 18:49:14 +0200 )edit

Replace 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.

m.a.riosv gravatar imagem.a.riosv ( 2013-03-31 22:13:57 +0200 )edit

Question Tools

Stats

Asked: 2013-03-31 08:28:54 +0200

Seen: 440 times

Last updated: Mar 31 '13