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

2

2kool4skool

New Member
Joined
Aug 18, 2009
Messages
21
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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Sort by date Sort by votes
2kool4skool said:
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-ignore:padding; 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-ignore:padding; 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" cellSpacing=0 cellPadding=0 width=195 border=0><COLGROUP><COL style="WIDTH: 65pt" span=3 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" width=65 height=15>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 class=xl63 style="HEIGHT: 15pt" height=15>animal</TD><TD class=xl63 style="BORDER-LEFT: medium none">color</TD><TD class=xl63 style="BORDER-LEFT: medium none">qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>cats</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">brown</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>cats</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">black</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>cats</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">white</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>dogs</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">brown</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>14</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>dogs</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">black</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>2</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>dogs</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">white</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>rabbits</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">brown</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>rabbits</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">black</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>rabbits</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">white</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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-ignore:padding; 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" cellSpacing=0 cellPadding=0 width=130 border=0><COLGROUP><COL style="WIDTH: 65pt" span=2 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" width=65 height=15>A</TD><TD style="WIDTH: 65pt" width=65>B</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="HEIGHT: 15pt" height=15>animal</TD><TD class=xl63 style="BORDER-LEFT: medium none">total qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>cats</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=middle>#N/A</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>dogs</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>rabbits</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" 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-ignore:padding; 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" cellSpacing=0 cellPadding=0 width=130 border=0><COLGROUP><COL style="WIDTH: 65pt" span=2 width=65><TBODY><TR style="HEIGHT: 15pt" height=15><TD style="WIDTH: 65pt; HEIGHT: 15pt" width=65 height=15>A</TD><TD style="WIDTH: 65pt" width=65>B</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="HEIGHT: 15pt" height=15>animal</TD><TD class=xl63 style="BORDER-LEFT: medium none">total qty</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>cats</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>dogs</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=15><TD class=xl63 style="BORDER-TOP: medium none; HEIGHT: 15pt" height=15>rabbits</TD><TD class=xl63 style="BORDER-TOP: medium none; BORDER-LEFT: medium none" 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
Click to expand...
What version of Excel are you using?
 
Upvote 0
2kool4skool said:
2010 on windows 7 and 2011 on mac os,
sorry, should have mentioned that
Steve
Click to expand...
Try this entered in B2 and copied down...

=IF(COUNTIFS(D$2:D$10,A2,F$2:F$10,"#N/A")=COUNTIF(D$2:D$10,A2),#N/A,SUMIFS(F$2:F$10,D$2:D$10,A2,F$2:F$10,"<1E100"))
 
Upvote 0
thank you!
this worked in my sample problem. but I tried to apply it to another workbook and it didn't work for me.
what is this part of the formula doing?
"<1E100"
 
Upvote 0
sorry, should have searched the forums again before asking the question. found out what 1E100 means, realize what it's doing in the formula and everything IS working fine on my other workbooks. this is great! thanks again
Steve
 
Upvote 0
2kool4skool said:
thank you!
this worked in my sample problem. but I tried to apply it to another workbook and it didn't work for me.
what is this part of the formula doing?
"<1E100"
Click to expand...
That is scientific notation for a very large number. 1 followed by 100 zeros.

That's part of the criteria for the SUMIFS function.

You'll need to be more specific about what "didn't work for me" means.

The formula will only work in Excel versions 2007 and later. I have no idea if it'll work on an Apple.
 
Upvote 0
2kool4skool said:
sorry, should have searched the forums again before asking the question. found out what 1E100 means, realize
what it's doing in the formula and everything IS working fine on my other
workbooks. this is great! thanks again
Steve
Click to expand...
Good deal! Thanks for the feedback! :cool:
 
Upvote 0
Here's a slightly more direct formula for B2 that I still think does what you want.

=IF(COUNTIFS(D$2:D$10,A2,F$2:F$10,">=0"),SUMIFS(F$2:F$10,D$2:D$10,A2,F$2:F$10,">=0"),#N/A)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
617
smozgur
smozgur
A
  • Question Question
VBA - Automatically Break the Line in HTML format.
Replies
7
Views
2K
Attrazion
A
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
E
  • Question Question
Query in excel to Outlook mail take rows with name until next name
Replies
0
Views
518
esben
E
E
  • Question Question
Create mails with table - standard header specific row
Replies
1
Views
1K
esben
E

Forum statistics

Threads
1,224,521
Messages
6,179,280
Members
452,902
Latest member
Knuddeluff

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top