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.
