Counting cells that does not contain

JODomingos

New Member
Joined
Jul 11, 2020
Messages
48
Office Version
  1. 2016
Platform
  1. Windows
Hello Friends!
I have been trying to create a formula to count how many cells in the row with specific text content that is different of reference(Column EA and EB).
For instance below: second line my reference is G and T, then I would like to count only cells that does not have those content G and T(Yellow cells) and fill the result in column EC.

Can you help me?


1600086164562.png
 

Some videos you may like

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

Does this do what you want? (You may need to confirm with Ctrl+Shift+Enter, not just Enter)

20 09 14.xlsm
DVDWDXDYDZEAEBEC
1
2TTTATTTATTGT2
3GCGGGGGGGGAG1
4AAAAATATAAAC2
5TTTCTCTCTTGT3
Count
Cell Formulas
RangeFormula
EC2:EC5EC2=COUNTA(DV2:DZ2)-COUNT(-SUBSTITUTE(SUBSTITUTE(DV2:DZ2,EA2,1),EB2,1))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,840
Members
413,944
Latest member
3xc3ln00b

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
Top