Count If with same keywords

[Demona26]

Hi all. I need Excel to count lines that include the phrase "test & 10", but not to count other lines that only have the word "test" without the & 10
I'm finding that I get zero results for "test & 10" as the keyword 'test' is filtered out in the 1st argument.
The formula I've tried is:

=COUNTIFS(Sheet2!$A:$A,Sheet1!A1,Sheet2!$D:$D,"<>*care*")+COUNTIFS(Sheet2!$A:$A,Sheet1!A1,Sheet2!$D:$D,"*&*")

any suggestions?

 

[Aryatect]

Hi, From the problem you described below should work:

Book1
	A	B	C
1	Something		2
2	something something test
3	something something test & 11
4
5	something something test & 10
6
7	something something test & 10
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF($A$1:$A$7,"*test & 10*")

 

[Demona26]

Hi Aryatect. This almost works - it is now counting everything that says "test & 10" but is not counting lines that do not have 'test'.
In your above sheet, if line A4 and A6 had text, the formula won't count them.

So, if I fill in the above matrix with the below, the answer in C1 is still 2 when it should be 5 (formula would count line 1,4,5,6,7)
[TABLE="class: grid, width: 300, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Something[/TD]
[TD][/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]something something test[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]something something test & 11[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]any other text[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]something something test & 10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]other stuff[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]something something test & 10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 100, align: left"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Aryatect]

Hi, Please try below:

Book1
	A	B	C
1	Something		5
2	something something test
3	something something test & 11
4	any other text
5	something something test & 10
6	other stuff
7	something something test & 10
Sheet1

Cell Formulas
Range	Formula
C1	=COUNTIF($A$1:$A$7,"<>*test*")+COUNTIF($A$1:$A$7,"*test & 10*")

 

[Demona26]

Awesome, thank you! I had tried to add the CountIfs, but I had the syntax wrong to exclude the word "test". Thank you so much

 

[Aryatect]

Glad could help and thanks for the feedback!