Excel - COLUMN Function Problem - Expert Solution

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

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.