SUMIF ignore #N/A but only if all criteria are #N/A

[2kool4skool]

I don't know if I phrased the title correctly but here is the problem

I have one table with the quantity of each color of each animal.
the qty column is a vlookup formula that is getting the quantities from another sheet.

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></style> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:195pt" border="0" cellpadding="0" cellspacing="0" width="195"> <col style="width:65pt" span="3" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td style="height:15.0pt;width:65pt" height="15" width="65">D</td> <td style="width:65pt" width="65">E</td> <td style="width:65pt" width="65">F</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt" height="15">animal</td> <td class="xl63" style="border-left:none">color</td> <td class="xl63" style="border-left:none">qty</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none">brown</td> <td class="xl63" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none">black</td> <td class="xl63" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none">white</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none">brown</td> <td class="xl63" style="border-top:none;border-left:none" align="right">14</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none">black</td> <td class="xl63" style="border-top:none;border-left:none" align="right">2</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none">white</td> <td class="xl63" style="border-top:none;border-left:none" align="right">0</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none">brown</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none">black</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none">white</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> </tbody></table>
<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --></style> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></style>

my results table looks like this

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></style> <style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:130pt" border="0" cellpadding="0" cellspacing="0" width="130"> <col style="width:65pt" span="2" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td style="height:15.0pt;width:65pt" height="15" width="65">A</td> <td style="width:65pt" width="65">B</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt" height="15">animal</td> <td class="xl63" style="border-left:none">total qty</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> </tbody></table>

the formula in column B is
=SUMIF(D4:D12,A2,F4:F12)

I'm not getting the results I want. I want it to add the total quantity for each type of animal (all colors). But if the results for all colors of a certain type of animal are all "#N/A" then I want it to return "#N/A"

here is an example of how I would like the results to be

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:130pt" border="0" cellpadding="0" cellspacing="0" width="130"> <col style="width:65pt" span="2" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td style="height:15.0pt;width:65pt" height="15" width="65">A</td> <td style="width:65pt" width="65">B</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt" height="15">animal</td> <td class="xl63" style="border-left:none">total qty</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none" align="center">#N/A</td> </tr> </tbody></table>
I have searched the forums and found examples of SUM(IF and SUMIFS formulas but I can't quite figure them out
any help would be appreciated.
thanks
Steve

 

[Aladin Akyurek]

I don't know if I phrased the title correctly
but here is the problem

I have one table with the quantity of each color of each animal.
the qty column is a vlookup formula that is getting the quantities from
another sheet.

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></STYLE><STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 195pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=195><COLGROUP><COL style="WIDTH: 65pt" span=3 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" height=15 width=65>D</TD><TD style="WIDTH: 65pt" width=65>E</TD><TD style="WIDTH: 65pt" width=65>F</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" class=xl63 height=15>animal</TD><TD style="BORDER-LEFT: medium none" class=xl63>color</TD><TD style="BORDER-LEFT: medium none" class=xl63>qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>brown</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>black</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>white</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>brown</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>14</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>black</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>white</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>brown</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>black</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63>white</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR></TBODY></TABLE>
<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} --></STYLE><STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></STYLE>

my results table looks like this

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --></STYLE><STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 130pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130><COLGROUP><COL style="WIDTH: 65pt" span=2 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" height=15 width=65>A</TD><TD style="WIDTH: 65pt" width=65>B</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" class=xl63 height=15>animal</TD><TD style="BORDER-LEFT: medium none" class=xl63>total qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR></TBODY></TABLE>

the formula in column B is
=SUMIF(D4:D12,A2,F4:F12)

I'm not getting the results I want. I want it to add the total quantity for each
type of animal (all colors). But if the results for all colors of a certain type of
animal are all "#N/A" then I want it to return "#N/A"

here is an example of how I would like the results to be

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 130pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130><COLGROUP><COL style="WIDTH: 65pt" span=2 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" height=15 width=65>A</TD><TD style="WIDTH: 65pt" width=65>B</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt" class=xl63 height=15>animal</TD><TD style="BORDER-LEFT: medium none" class=xl63>total qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=middle>#N/A</TD></TR></TBODY></TABLE>
I have searched the forums and found examples of SUM(IF and SUMIFS
formulas but I can't quite figure them out
any help would be appreciated.
thanks
Steve

In B2 enter and copy down:

=SUM(SUMIFS($F$2:$F$10,$F$2:$F$10,{"<0",">0"},$D$2:$D$10,A2))

where A2 houses cats.

 

[2kool4skool]

Seems like a few different ways to skin this cat. pun intended, shamelessly .

they all work. is one of these formulas better (more efficient) than the others for some reason?

Peter, you mentioned that your formula was a slightly more efficient way of doing T. Valko's formula, but then Aladin's formula is different, but also works, and is shorter. at least it has less characters. (thank you all).

is there a rule of thumb which says than the shorter a formula is, the more efficient it is? or are certain functions inherently more efficient than others?

I understand "more efficient" to mean calculating faster, especially when used on thousands of cells.

 

[2kool4skool]

just a follow up, I was mistaken on your formula, Aladin. I didn't test it properly the first time.
the formula gives me this result
=SUM(SUMIFS($F$2:$F$10,$F$2:$F$10,{"<0",">0"},$D$2:$D$10,A2))

<style> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </style> <table style="border-collapse: collapse;width:130pt" border="0" cellpadding="0" cellspacing="0" width="130"> <col style="width:65pt" span="2" width="65"> <tbody><tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;width:65pt" height="15" width="65">animal</td> <td class="xl63" style="border-left:none;width:65pt" width="65">qty</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">cats</td> <td class="xl63" style="border-top:none;border-left:none" align="right">12</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">dogs</td> <td class="xl63" style="border-top:none;border-left:none" align="right">16</td> </tr> <tr style="height:15.0pt" height="15"> <td class="xl63" style="height:15.0pt;border-top:none" height="15">rabbits</td> <td class="xl63" style="border-top:none;border-left:none" align="right">0</td> </tr> </tbody></table>
but I need to get a "#N/A" for the "rabbits" because all 3 of the "rabbits" colors are "#N/A"
Steve

 

[Aladin Akyurek]

Seems like a few different ways to skin this cat. pun intended, shamelessly .

they all work. is one of these formulas better (more efficient) than the others
for some reason?

Peter, you mentioned that your formula was a slightly more efficient way of
doing T. Valko's formula, but then Aladin's formula is different, but also
works, and is shorter. at least it has less characters. (thank you all).

is there a rule of thumb which says than the shorter a formula is, the more
efficient it is? or are certain functions inherently more efficient than others?

No, not necessarily. The nature of calculations a formula carries out is
the determining factor.

I understand "more efficient" to mean calculating faster, especially when used on thousands of cells.

Yes, quite so...

 

[Aladin Akyurek]

just a follow up, I was mistaken on your formula, Aladin. I didn't test it properly the first time.
the formula gives me this result
=SUM(SUMIFS($F$2:$F$10,$F$2:$F$10,{"<0",">0"},$D$2:$D$10,A2))

<STYLE> <!--table {mso-displayed-decimal-separator:"\."; mso-displayed-thousand-separator:"\,";} @page {margin:1.0in .75in 1.0in .75in; mso-header-margin:.5in; mso-footer-margin:.5in;} td {padding-top:1px; padding-right:1px; padding-left:1px; mso-ignoreadding; color:black; font-size:12.0pt; font-weight:400; font-style:normal; text-decoration:none; font-family:Calibri, sans-serif; mso-font-charset:0; mso-number-format:General; text-align:general; vertical-align:bottom; border:none; mso-background-source:auto; mso-pattern:auto; mso-protection:locked visible; white-space:nowrap; mso-rotate:0;} .xl63 {border:.5pt solid windowtext;} --> </STYLE><TABLE style="WIDTH: 130pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=130><COLGROUP><COL style="WIDTH: 65pt" span=2 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" class=xl63 height=15 width=65>animal</TD><TD style="BORDER-LEFT: medium none; WIDTH: 65pt" class=xl63 width=65>qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>cats</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>dogs</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=15><TD style="HEIGHT: 15pt; BORDER-TOP: medium none" class=xl63 height=15>rabbits</TD><TD style="BORDER-LEFT: medium none; BORDER-TOP: medium none" class=xl63 align=right>0</TD></TR></TBODY></TABLE>
but I need to get a "#N/A" for the "rabbits" because all 3 of the "rabbits" colors are "#N/A"
Steve

This formula always return a number, hence 0 when none available.

Note. I believe you can give some hard returns when typing text in order
to reduce the consequent scrolling.

 

[2kool4skool]

Thank you all for your help, you have been very generous with your expertise.
Steve

 

[Peter_SSs]

Peter, you mentioned that your formula was a slightly more efficient way of doing T. Valko's formula,..

Firstly I'll qualify my response by stating that I am no expert in efficiency of formulas.

However, my understanding is that ..

- You certainly cannot equate efficiency to the physical length (keystrokes) in a formula.

- To some extent the number of functions used in a formula is a guide (ie less functions = greater efficiency) but that is also not always correct since
some functions require much more "processing resources" than others.

I made the particular comment in this thread because Biff's formula required a COUNTIFS, a COUNTIF and a SUMIFS function in the formula. Mine used a similar COUNTIFS and
a similar SUMIFS but no COUNTIF and that's why I said it was 'slightly more direct'.

 

[2kool4skool]

I see Peter
sorry I misquoted you. I see that you wrote "slightly more direct"

I have made more progress on this project in one day than I have in a whole month (not exaggerating!) thanks to you, Aladin, Biff, and others, not to mention the vast knowledge-base that is the forum and it's search function.

thanks you so much again.
Steve

 

[Peter_SSs]

I have made more progress on this project in one day than I have in a whole month (not exaggerating!) thanks to you, Aladin, Biff, and others, not to mention the vast knowledge-base that is the forum and it's search function.

thanks you so much again.
Steve

Glad you are getting benefit from your time here. It is always good to get such positive feedback.

 

[T. Valko]

Seems like a few different ways to skin this cat. pun intended, shamelessly .

they all work. is one of these formulas better (more efficient) than the others for some reason?

Peter, you mentioned that your formula was a slightly more efficient way of doing T. Valko's formula, but then Aladin's formula is different, but also works, and is shorter. at least it has less characters. (thank you all).

is there a rule of thumb which says than the shorter a formula is, the more efficient it is? or are certain functions inherently more efficient than others?

I understand "more efficient" to mean calculating faster, especially when used on thousands of cells.

Efficiency is a relative concept.

Your first priority is to make sure the formula does exactly what you need it to do and does it accuately.

After that, then you can take efficiency into account if you need to.

For example, if your file is 25 kb in size and only does a "few" calculations then efficiency is probably not a big concern to you. On the other hand, if your file is 10 mb in size and does 1000's of calculations then you probably need to be as efficient as possible.

Some people are obsessed with efficiency to a fault. I say: don't do more than you need to do. For example, in a forum like this, when someone posts a question they almost never give a complete description of their data and the data layout and what all the possibilities may be.

You'll get replies where folks like to try to account for all possibilities without knowing what all those possibilites might be. If you won't have empty cells in a range then you don't need to account for empty cells in a range. The poster might not have mentioned that yet some folks will still insist on accounting for that when it might not be needed.

All of this leads to formula bloat, obfuscation and convolution! Some folks are masters at this.

That in itself is inefficient.

If one formula takes 0.000032 seconds to calculate and another formula that is written differently but does the exact same thing takes 0.000035 seconds to calculate, can you tell the difference?

And then there's the use of array formulas and volatile functions.

Some folks avoid both to a fault, as if you'll catch some sort of disease from them!

Shorter is not always better. However, shorter does save you from more typing and that's a good thing if you type like me.

That being said, I like Peter's suggestion more than my own.

Tips on efficiency:

http://www.decisionmodels.com/