count values of a condition, based on values of another condition in separate column

[d_carr]

my question:

in cell U2, im trying to return a value based on:

Where column Q = "R"
Count "*RWRK*" in column N
end statement

 

[VoG]

Try

=SUMPRODUCT(--(ISNUMBER(FIND("RWRK",N1:N10))),--(Q1:Q10="R"))

 

[Blade Hunter]

Another option:

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >N</td><td >O</td><td >P</td><td >Q</td><td >R</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >RWRKrfff</td><td > </td><td > </td><td >R</td><td style="text-align:right; ">2</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >RWRK</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >OOO</td><td > </td><td > </td><td >R</td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >DDD</td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >RWRK</td><td > </td><td > </td><td >R</td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >R1</td><td >{=SUM(<span style=' color:008000; '>(NOT<span style=' color:#0000ff; '>(SUBSTITUTE<span style=' color:#ff0000; '>(N1:N5,"RWRK","")</span>=N1:N5)</span>*<span style=' color:#0000ff; '>(Q1:Q5="R")</span>)</span>)}</td></tr></table></td></tr><tr><td ><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>Formula Array:</span><span style=' font-family:Arial,Arial; font-size:9pt;'><br />Produce enclosing </span><span style=' font-family:Arial,Arial; font-size:9pt; font-weight:bold;'>{ }</span><span style=' font-family:Arial,Arial; font-size:9pt;'> by entering<br />formula with CTRL+SHIFT+ENTER!</span></td></tr></table> <br />Excel tables to the web - Excel Jeanie Html 4

 

[d_carr]

VoG - thank u very much - correct results showing - much appreciated!
Blade Hunter - thank u also

 

[d_carr]

second question:

in cell Y1, im trying to return a value based on:

Count values in column Q that equal "P" or "PS"
end statement

 

[Blade Hunter]

=countif(Q:Q,"P")+countif(Q:Q,"PS")

 

[d_carr]

Blade Hunter - much appreciated, i didnt know u could + countif's

third question open to all:

based on VoG's previous answer,
in cell Y2, im trying to return a value based on:

Where column Q values = "P" or "PS"
Count "*RWRK*" in column N values
end statement

 

[VoG]

Try

=SUMPRODUCT(--(ISNUMBER(FIND("RWRK",N1:N10))),--(ISNUMBER(MATCH(Q1:Q10,{"P","PS"},0))))

 

[d_carr]

VoG - luv ur work - ur answer works like a charm - gold!