countifs

[nadja]

Hi,
I'm trying to create a countif formula that takes into account 3 different criteria:

countif
a) column R equals "delivery" and
b) column S begins with 2 or begins with 8

So far I've come up with this "if" formula, but excel doesn't seem to like the "or" in it:
IF(OR('Adjustments Dec 2011'!S:S="2*",'Adjustments Dec 2011'!S:S="8*"),COUNTIF('Adjustments Dec 2011'!R:R,'Adjustments Dec 2011'!R:R="DELIVERY"))

I hope this makes sense. Any help is highly appreciated!

 

[MrVillareal]

I've tried both the sumproduct formulas but only get result #n/a.

Has it maybe got to do witht he fact that some of the results in column s are #n/a?

yes your correct..however try this,

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>R</th><th>S</th><th>T</th><th>U</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style=";">delivery</td><td style="text-align: right;;">28</td><td style="text-align: right;;"></td><td style="text-align: right;background-color: #FFFF00;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">adsf</td><td style="text-align: right;;">2</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=";">delivery</td><td style=";">2a</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=";">delivery</td><td style=";">8a</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=";">delivery</td><td style="text-align: right;;">#N/A</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=";">delivery</td><td style="text-align: right;;">3</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=";">delivery</td><td style="text-align: right;;">0</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=";">delivery</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;">9</td><td style=";">delivery</td><td style=";">b</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet6</p><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>Array 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: #BBB"><thead><tr style=" background-color: #DAE7F5;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: #DAE7F5;color: #161120">U1</th><td style="text-align:left">{=SUM(<font color="Blue">(<font color="Red">R1:R9="delivery"</font>)*(<font color="Red">IF(<font color="Green">ISNUMBER(<font color="Purple">LEFT(<font color="Teal">S1:S9,1</font>)+0</font>),LEFT(<font color="Purple">S1:S9,1</font>)+0</font>)=2</font>)+(<font color="Red">IF(<font color="Green">ISNUMBER(<font color="Purple">LEFT(<font color="Teal">S1:S9,1</font>)+0</font>),LEFT(<font color="Purple">S1:S9,1</font>)+0</font>)=8</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[barry houdini]

I was reluctant to suggest COUNTIFS because wildcards won't work with numeric data but if your column S data is always alphanumeric then you can use this formula

=SUM(COUNTIFS('Adjustments Dec 2011'!R$2:R$100,"Delivery",'Adjustments Dec 2011'!S$2:S$100,{"2","8"}&"*"))

....and that will cope with #N/A errors too.....

 

[MrVillareal]

I was reluctant to suggest COUNTIFS because wildcards won't work with numeric data but if your column S data is always alphanumeric then you can use this formula

=SUM(COUNTIFS('Adjustments Dec 2011'!R$2:R$100,"Delivery",'Adjustments Dec 2011'!S$2:S$100,{"2","8"}&"*"))

....and that will cope with #N/A errors too.....

Hi Barry how does your formula work for the first character of column S?

 

[barry houdini]

The criteria part for column S is this

{"2","8"}&"*"

that's 2 or 8 followed by anything. In fact the quotes around the numbers are unnecessary so it can be just.....

=SUM(COUNTIFS('Adjustments Dec 2011'!R$2:R$100,"Delivery",'Adjustments Dec 2011'!S$2:S$100,{2,8}&"*"))

 

[MrVillareal]

The criteria part for column S is this

{"2","8"}&"*"

that's 2 or 8 followed by anything. In fact the quotes around the numbers are unnecessary so it can be just.....

=SUM(COUNTIFS('Adjustments Dec 2011'!R$2:R$100,"Delivery",'Adjustments Dec 2011'!S$2:S$100,{2,8}&"*"))

I got it..my assumption is that for column S there are numbers and texts and with some errors but you assumed alphanumeric which is also text..

Thank you Barry.

 

[barry houdini]

Yes, I assumed all text values but to allow the possibility of numbers in column S......and possibly errors too you can use SUMPRODUCT like this

=SUMPRODUCT((R1:R9="delivery")*ISNUMBER(MATCH(LEFT(S1:S9)+0,{2,8},0)))

 

[MrVillareal]

Yes, I assumed all text values but to allow the possibility of numbers in column S......and possibly errors too you can use SUMPRODUCT like this

=SUMPRODUCT((R1:R9="delivery")*ISNUMBER(MATCH(LEFT(S1:S9)+0,{2,8},0)))

Wow very nice Barry I never thought of using MATCH. Thank you Barry I was reminded of using Array Syntax and the default of LEFT is 1 if omitted.