COUNTIF

%3CLINGO-SUB%20id%3D%22lingo-sub-2882284%22%20slang%3D%22en-US%22%3ECOUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2882284%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20need%20to%20count%20the%20amount%20of%20times%202%20words%20appear%20in%20each%20row%20in%20a%20column.%20(See%20image%20attached)%20I%20need%20to%20calculate%20how%20many%20times%20%22Diamond%22%20and%20%22Medium%22%20appear.%20I%20have%20been%20trying%20to%20use%20the%20COUNTIF%20formula%20and%20I%20was%20only%20able%20to%20count%20how%20many%20times%20Diamond%20appears%20(one%20word%20or%20value).%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EReally%20appreciate%20it%20in%20advance!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2882284%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2882363%22%20slang%3D%22en-US%22%3ERe%3A%20COUNTIF%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2882363%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1195419%22%20target%3D%22_blank%22%3E%40Elther%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUM(--ISNUMBER(SEARCH(%22Diamond%22%2CA2%3AA9))%2C--ISNUMBER(SEARCH(%22Medium%22%2CA2%3AA9)))%3C%2FP%3E%3CP%3EIs%20this%20the%20formula%20you%20are%20looking%20for%3F%20It%20has%20to%20be%20entered%20as%20arrayformula%20with%20ctrl%2Bshift%2Benter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Hi, 


I need to count the amount of times 2 words appear in each row in a column. (See image attached) I need to calculate how many times "Diamond" and "Medium" appear. I have been trying to use the COUNTIF formula and I was only able to count how many times Diamond appears (one word or value). 

 

Can someone help me with this?

 

Really appreciate it in advance!

2 Replies

@Elther 

=SUM(--ISNUMBER(SEARCH("Diamond",A2:A9)),--ISNUMBER(SEARCH("Medium",A2:A9)))

Is this the formula you are looking for? It has to be entered as arrayformula with ctrl+shift+enter.

@Elther 

That could be like

=COUNTIF(A2:A9, "*Diamond*")