Multiple Criteria for IF statement

[Loin75]

I don't think what I am going to ask is possible....

I have a lot of data in a spreadsheet. I want a formula to count how many rows meet the following criteria:

column B > 100
column C = Car, Bus, or Lorry.

Any help greatly apprciated.

 

[MrKowz]

Never underestimate the power of the Excel Side.

Try:

=SUMPRODUCT(--(B1:B100>100),--(C1:C100={"Car","Bus","Lorry"}))

 

[njimack]

=SUMPRODUCT(--(B2:B100>100),--(ISNUMBER(MATCH($C$2:$C$100,{"Car","Bus","Lorry"},0))))

 

[Loin75]

Amazing...

MrKowz - you were close, but it #REF'd me..

njimack - spot on mate...

Thank you to you both - I will never under estimate the power of excel

 

[Ron Coderre]

For an approach with some flexibility....

Col_B contains numeric values
Col_C contains words

D1: a value to exceed....eg 100
E1:E3 contains the words to match
Car
Bus
Lorry

This formula returns the count of Col_B items greater than D1
where the corresponding Col_C items match one of the E1:E3 words:

F1: =SUMPRODUCT((B3:B100>D1)*(COUNTIF(E1:E3,C3:C100)))

Adjust ranges to suit your situation.

Note: If you have Excel 2007 or later, you can use this formula...but it exacts a performance price:

F1: =SUMPRODUCT((B:B>D1)*(COUNTIF(E1:E3,C:C)))

Is that something you can work with?

 

[Loin75]

very much so... I have many formulas I still need to design to extract information from this spreadhseet. This was just the start...

I will have a play with this now.

Many Thanks

 

[MrKowz]

Amazing...

MrKowz - you were close, but it #REF'd me..

njimack - spot on mate...

Thank you to you both - I will never under estimate the power of excel

I forgot that I can't use the double unary operator when trying to test column C in that manner. This works:

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">105</td><td style=";">BUS</td><td style=";">Count:</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">130</td><td style=";">TRUCK</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">110</td><td style=";">CAR</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">125</td><td style=";">LORRY</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">51</td><td style=";">BUS</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">53</td><td style=";">TRUCK</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: right;;">132</td><td style=";">LORRY</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: right;;">57</td><td style=";">LORRY</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: right;;">82</td><td style=";">TRUCK</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: right;;">138</td><td style=";">CAR</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>Sheet1</b></th></tr></td></thead></table><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><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 width="10px" style=" background-color: #E0E0F0;color: #161120">E1</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">B1:B10>100</font>)*(<font color="Red">C1:C10={"CAR","BUS","LORRY"}</font>)</font>)</td></tr></tbody></table></td></tr></table><br />