**Question description:**

*This user has given permission to use the problem statement for this blog.*

I need a formula to count cells with a value containing the word "arc" or the word "robot". The cells are all in the same column, but some of the cells contain both words so I get duplicates in my count.

Solved by M. W. in 39 mins

This is the chat thread from the real Excelchat help session. It contains no private user information.

Excelchat Expert
02/02/2018 - 11:45

Welcome to Got It Pro Excel!

User
02/02/2018 - 11:48

ok

Excelchat Expert
02/02/2018 - 11:48

I am glad to be able to help you

Excelchat Expert
02/02/2018 - 11:48

Do you have any example?

User
02/02/2018 - 11:48

I don't have a file

Excelchat Expert
02/02/2018 - 11:48

Ok

Excelchat Expert
02/02/2018 - 11:50

How are you trying to do it?

User
02/02/2018 - 11:52

I've tried a few different ways. Countifs and countif + countif don't seem to work. I must be doing something wrong.

Excelchat Expert
02/02/2018 - 11:52

OK, so you want to know how many cells has robot or arc

User
02/02/2018 - 11:53

yes

Excelchat Expert
02/02/2018 - 11:53

In this case the result should be 6

Excelchat Expert
02/02/2018 - 11:53

right?

User
02/02/2018 - 11:53

yes

Excelchat Expert
02/02/2018 - 11:53

Got it

Excelchat Expert
02/02/2018 - 11:53

Let me see

User
02/02/2018 - 11:57

I think I just figured it out

Excelchat Expert
02/02/2018 - 11:57

Really

Excelchat Expert
02/02/2018 - 11:57

?

Excelchat Expert
02/02/2018 - 11:57

Thats a good solution!

User
02/02/2018 - 11:57

formula in cell c2

Excelchat Expert
02/02/2018 - 11:58

Yes I see!

Excelchat Expert
02/02/2018 - 11:58

Good work!

User
02/02/2018 - 11:58

It only works if the order is right.

Excelchat Expert
02/02/2018 - 11:58

Let me try something

User
02/02/2018 - 11:59

Ok. But I could subtract twice to eliminate duplicates both ways I guess.

Excelchat Expert
02/02/2018 - 11:59

You could

Excelchat Expert
02/02/2018 - 11:59

Have you ever used array formulas?

User
03/02/2018 - 12:00

No. I'm kind of learning on the fly for a new job.

Excelchat Expert
03/02/2018 - 12:00

I see you are very good with formulas

Excelchat Expert
03/02/2018 - 12:00

I think Arrays formulas could solve this problem

User
03/02/2018 - 12:01

Google usually helps but this one is stumping me.

Excelchat Expert
03/02/2018 - 12:01

OK

User
03/02/2018 - 12:01

Can you show me?

Excelchat Expert
03/02/2018 - 12:01

Of course

Excelchat Expert
03/02/2018 - 12:02

give me a moment to make de formula

Excelchat Expert
03/02/2018 - 12:04

I am working with you

User
03/02/2018 - 12:04

ok

Excelchat Expert
03/02/2018 - 12:08

Almost there

Excelchat Expert
03/02/2018 - 12:13

Arrays IF only works with one condition

Excelchat Expert
03/02/2018 - 12:13

trying a work arround

User
03/02/2018 - 12:14

Ok. I will be having more complex counts than this so I hope there is a solution.

Excelchat Expert
03/02/2018 - 12:16

Ok

Excelchat Expert
03/02/2018 - 12:17

=ArrayFormula(SUM(IF(ISNUMBER(FIND("arc",A1:A10))+ISNUMBER(FIND("robot",A1:A10)),1)))

Excelchat Expert
03/02/2018 - 12:18

In excel you need to press CTRL+SHIFT+ENTER to enter arrays formulas

Excelchat Expert
03/02/2018 - 12:18

if it finds "arc" or "robot" will count 1 in the array

Excelchat Expert
03/02/2018 - 12:19

Do you have Excel Desktop?

User
03/02/2018 - 12:19

So if I had another criteria I could just add another ISNUMBER?

Excelchat Expert
03/02/2018 - 12:20

ISNUMBER(FIND("arc",A1:A10)) this is one criteria

Excelchat Expert
03/02/2018 - 12:20

ISNUMBER(FIND("robot",A1:A10)) this another criteria

User
03/02/2018 - 12:20

I don't have Excel on my home computer. I'll have to wait until I go back to work.

Excelchat Expert
03/02/2018 - 12:20

and you have to separate them by "+" inside of the if

Excelchat Expert
03/02/2018 - 12:21

Ok, you can add more criterias

User
03/02/2018 - 12:21

Excellent! Thank you!

**This is the output file from the real Excelchat help session:**

*This is an example of the expert help you can get. It contains no private user information.*