Count If with same keywords

Demona26

New Member
Joined
Feb 22, 2018
Messages
40
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Hi, From the problem you described below should work:


Book1
ABC
1Something2
2something something test
3something something test & 11
4
5something something test & 10
6
7something something test & 10
Sheet1
Cell Formulas
RangeFormula
C1=COUNTIF($A$1:$A$7,"*test & 10*")
 
Upvote 0
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)
ABC
1Something2
2something something test
3something something test & 11
4any other text
5something something test & 10
6other stuff
7something something test & 10

<tbody>
</tbody>

<tbody>
</tbody>
 
Upvote 0
Hi, Please try below:


Book1
ABC
1Something5
2something something test
3something something test & 11
4any other text
5something something test & 10
6other stuff
7something something test & 10
Sheet1
Cell Formulas
RangeFormula
C1=COUNTIF($A$1:$A$7,"<>*test*")+COUNTIF($A$1:$A$7,"*test & 10*")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top