formula help please

[coltheplumb]

Hi all I have this formula =COUNTIF(D16:H16,"*C*")/5+SUMPRODUCT(--ISNUMBER(D16:H16))/5 now what I want to do is to count if cell D16:H16 has a “C” or an “O” in it.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Thanks for your help <o></o>
Colin X<o></o>

 

[JazzSP8]

Like this?

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col><col><col><col><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">16</td><td style=";">C</td><td style=";">O</td><td style=";">C</td><td style=";">O</td><td style=";">C</td></tr><tr><td style="color: #161120;text-align: center;">17</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">18</td><td style="text-align: right;;">3</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">19</td><td style="text-align: right;;">2</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td><td style="text-align: right;;">
</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D18</th><td style="text-align:left">=COUNTIF(D16:H16,"C")</td></tr><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">D19</th><td style="text-align:left">=COUNTIF(D16:H16,"O")</td></tr></tbody></table></td></tr></tbody></table>

 

[pgc01]

Hi Colin

Instead of

COUNTIF(D16:H16,"*C*")

use

SUM(COUNTIF(D16:H16,"*"&{"C","O"}&"*"))

if the cells do not countain both letters

 

[Soggy]

I went a slightly diffrent way with:

=SUMPRODUCT(COUNTIF(D16:H16,{"*O*","*C*"}))

 

[pgc01]

I went a slightly diffrent way with:

=SUMPRODUCT(COUNTIF(D16:H16,{"*O*","*C*"}))

Hi

No need for SumProduct()

=SUM(COUNTIF(D16:H16,{"*O*","*C*"}))

 

[coltheplumb]

Well done guys
Thanks very much

Colin X