Ask Your Question
0

COUNTIF used finding words in multiple columns [closed]

asked 2012-04-01 19:55:13 +0200

Levity gravatar image

I'm basically trying to find the condition when "racecar" and "shoe" are both in the same row, but in two different columns. Most of my searching has brought me to similar questions for Open Office, but with solutions that don't appear to work.

I get Error 502 for this formula:

=COUNTIF((H2:H118,"racecar")*(I2:I118,"shoe"))

SUMPRODUCT has also been recommended, but I don't think that's what I'm looking for.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-11-14 19:40:21.559001

2 Answers

Sort by » oldest newest most voted
3

answered 2012-04-01 23:08:34 +0200

m.a.riosv gravatar image

updated 2012-04-03 03:04:29 +0200

I think the function to do this SUMPRODUCT()

=SUMPRODUCT(H2:H118="racecar";I2:I118="shoe")

If this words are not alone in the cell then:

=SUMPRODUCT(ISNUMBER(FIND("racecar";H2:H118));ISNUMBER(FIND("shoe";i2:i118)))

The use of ISNUMBER() is because FIND() return #VALUE! when find nothing.

If you need regular expression to search, then use SEARCH() instead of FIND()

edit flag offensive delete link more

Comments

Perfect. Thanks!

Levity gravatar imageLevity ( 2012-04-01 23:11:46 +0200 )edit

Is there a way to do this with number AND letters? Such as "1" replacing "shoe".

Levity gravatar imageLevity ( 2013-03-31 08:21:32 +0200 )edit

Please Levity, open a new thread explaining what you want achieve.

m.a.riosv gravatar imagem.a.riosv ( 2013-03-31 16:40:50 +0200 )edit
0

answered 2013-11-21 11:44:52 +0200

This is what i was looking for. Thank you so much

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-04-01 19:55:13 +0200

Seen: 19,152 times

Last updated: Nov 21 '13