COUNTIF Formula Help

[gymwrecker]

I need help wit ha formulat that counts any cells within a range that have a "3" or "4" in them. It only needs to be counted once, and wont matter if there's more than one "3" in the same cell. For instance:

A1:111321
B1: 133112
C1: 111111
D1: 111242
E:1 311121

Thanks!

 

[njimack]

Confirm with ctrl+shift+enter:
=SUM(IF(ISNUMBER(SEARCH(3,A1:E1)),1,0))+SUM(IF(ISNUMBER(SEARCH(4,A1:E1)),1,0))

 

[Trevor G]

I need help wit ha formulat that counts any cells within a range that have a "3" or "4" in them. It only needs to be counted once, and wont matter if there's more than one "3" in the same cell. For instance:

A1:111321
B1: 133112
C1: 111111
D1: 111242
E:1 311121

Thanks!

Try something like this

Sheet2

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Data</TD><TD>Data</TD><TD> </TD><TD style="TEXT-ALIGN: right; FONT-FAMILY: Verdana">10</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">1234</TD><TD style="TEXT-ALIGN: right">1234</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">2342</TD><TD style="TEXT-ALIGN: right">2342</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">12555</TD><TD style="TEXT-ALIGN: right">12555</TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">321</TD><TD style="TEXT-ALIGN: right">321</TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>D1</TD><TD>{=SUM(IF(ISNUMBER(SEARCH(3,A1:B5)),1,0))+SUM(IF(ISNUMBER(SEARCH(4,A1:B5)),1,0))}</TD></TR></TBODY></TABLE></TD></TR><TR><TD>Formula Array:
Produce enclosing { } by entering
formula with CTRL+SHIFT+ENTER!</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4

Just seen Neil has given a solution..

 

[njimack]

Just seen Neil has given a solution..

Great minds, eh?

 

[Trevor G]

Absolutely Neil.

Have a good weekend ....

 

[gymwrecker]

Thank you all!!!!

 

[T. Valko]

I need help wit ha formulat that counts any cells within a range that have a "3" or "4" in them. It only needs to be counted once, and wont matter if there's more than one "3" in the same cell. For instance:

A1:111321
B1: 133112
C1: 111111
D1: 111242
E:1 311121

Thanks!

Here's another one...

=SUMPRODUCT(--(MMULT(--(ISNUMBER(FIND({3,4},A1:A5))),{1;1})>0))

 

[planetpj]

Here's another one...

=SUMPRODUCT(--(MMULT(--(ISNUMBER(FIND({3,4},A1:A5))),{1;1})>0))

Very nice!

 

[T. Valko]

Very nice!

Thanks!

 

[barry houdini]

The suggested formulas do different things though, don't they?

Biff's will only count each cell once at most, Neil's and Trevor's suggestions will count any cell with a 3 and a 4 twice........

I assumed the former was the requirement but maybe not......