Compare two columns and return sum of items in one column

J

jmyeti

New Member
Joined
Aug 12, 2011
Messages
8
Hello Excel Gurus,

I have two columns with numbers sporadically loaded in each and I need to compare each row against each column to see if there is a value above zero, and then, if both have a value greater than zero, return the sum of the those items in the first column that had a counterpart in the second column.

I'm using A and B columns for my data and putting the the sum total of all the A column values that have a row mate in B column in cell D4. I only want to sum the values in column A if there is something present in the same row for column B, but it doesn't have to be the same value.

How do I do that? I'm trying to use arrays in a SUMIF formula, but it's not returning a value correctly.
=SUMIF(SIF!A6:A349, AND(SIF!A6:A349, SIF!B6:B349), SIF!A6:A349)

Any ideas?
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
Sort by date Sort by votes
jmyeti said:
Hello Excel Gurus,

I have two columns with numbers sporadically loaded in each and I need to compare each row against each column to see if there is a value above zero, and then, if both have a value greater than zero, return the sum of the those items in the first column that had a counterpart in the second column.

I'm using A and B columns for my data and putting the the sum total of all the A column values that have a row mate in B column in cell D4. I only want to sum the values in column A if there is something present in the same row for column B, but it doesn't have to be the same value.

How do I do that? I'm trying to use arrays in a SUMIF formula, but it's not returning a value correctly.
=SUMIF(SIF!A6:A349, AND(SIF!A6:A349, SIF!B6:B349), SIF!A6:A349)

Any ideas?
Click to expand...
Can you post a few rows of sample data and tell us what result you expect?
 
Upvote 0
A B C D1 = SUM(B1,B4,B7,B8,B11) (but dynamically)
<table border="0" cellpadding="0" cellspacing="0" width="165"><colgroup><col style="mso-width-source:userset;mso-width-alt:292;width:6pt" width="8"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> </colgroup><tbody><tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;width:6pt" height="17" width="8">
</td> <td class="xl93" style="border-left:none;width:61pt" width="81">
</td> <td class="xl93" style="border-left:none;width:57pt" width="76">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 1</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">50</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">50</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 2</td><td class="xl93" style="border-top:none;border-left:none">
</td> <td class="xl93" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 3</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">85</td> <td class="xl93" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 4</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">50</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">35</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 5</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">20</td> <td class="xl93" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 6</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">25</td> <td class="xl93" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 7</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">100</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">75</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 8</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">60</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">60</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 9</td> <td class="xl93" style="border-top:none;border-left:none">
</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">100</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 10</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">120</td> <td class="xl93" style="border-top:none;border-left:none">
</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 11</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">50</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">50</td> </tr> <tr style="mso-height-source:userset;height:12.75pt" height="17"> <td class="xl96" style="height:12.75pt;border-top:none" height="17"> 12</td> <td class="xl93" style="border-top:none;border-left:none">
</td> <td class="xl93" style="border-top:none;border-left:none; font-size:10.0pt;color:windowtext;font-weight:700;text-decoration:none; text-underline-style:none;text-line-through:none;font-family:Verdana; border:.5pt solid windowtext;background:#FCD5B4;mso-pattern:black none" align="right">40</td> </tr> </tbody></table>
 
Last edited:
Upvote 0
jmyeti said:
A B C D1 = SUM(B1,B4,B7,B8,B11) (but dynamically)
<TABLE cellSpacing=0 cellPadding=0 width=165 border=0><COLGROUP><COL style="WIDTH: 6pt; mso-width-source: userset; mso-width-alt: 292" width=8><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><COL style="WIDTH: 57pt; mso-width-source: userset; mso-width-alt: 2779" width=76></COLGROUP><TBODY><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="WIDTH: 6pt; HEIGHT: 12.75pt" width=8 height=17>

</TD><TD class=xl93 style="BORDER-LEFT: medium none; WIDTH: 61pt" width=81>

</TD><TD class=xl93 style="BORDER-LEFT: medium none; WIDTH: 57pt" width=76>

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>1</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>50</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>50</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>2</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>3</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>85</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>4</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>50</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>35</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>5</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>20</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>6</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>25</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>7</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>100</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>75</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>8</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>60</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>60</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>9</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>100</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>10</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>120</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>11</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>50</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>50</TD></TR><TR style="HEIGHT: 12.75pt; mso-height-source: userset" height=17><TD class=xl96 style="BORDER-TOP: medium none; HEIGHT: 12.75pt" height=17>12</TD><TD class=xl93 style="BORDER-TOP: medium none; BORDER-LEFT: medium none">

</TD><TD class=xl93 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 700; FONT-SIZE: 10pt; BACKGROUND: #fcd5b4; BORDER-LEFT: windowtext 0.5pt solid; COLOR: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Verdana; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" align=right>40</TD></TR></TBODY></TABLE>
Click to expand...
One way...

Book1
ABCD
15050_270
2____
385___
45035__
520___
625___
710075__
86060__
9_100__
10120___
115050__
12_40__
Sheet1

This formula entered in D1:

=SUMPRODUCT(--(A1:A12<>""),--(B1:B12<>""),B1:B12)

What version of Excel are you using?
 
Upvote 0
T. Valko said:
One way...

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"><COL style="WIDTH: 29px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">270</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">85</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">35</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">20</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">6</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">25</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">7</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">100</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">75</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">8</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">60</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">60</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">100</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">10</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">120</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">11</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">50</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">12</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">40</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; COLOR: #ffffff; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">_</TD></TR></TBODY></TABLE>


This formula entered in D1:

=SUMPRODUCT(--(A1:A12<>""),--(B1:B12<>""),B1:B12)

What version of Excel are you using?
Click to expand...
That formula will work in ALL versions of Excel.

This one will work in versions 2007 and later:

=SUMIFS(B1:B12,A1:A12,"<>",B1:B12,"<>")
 
Upvote 0
Excel 2007 with Windows 7. That appears to be working. Thank you! So I can understand what this is doing, can you summarize what the '--' operator does?
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
SUM of Columns based on multiple criteria with User Form
2
Replies
15
Views
329
Alex Blakenburg
A
M
  • Question Question
Sum together the larger amount of two ranges by row
Replies
3
Views
70
Cubist
Cubist
M
  • Solved
Sum only the unique values from one column within a range of duplicate values from another column
Replies
7
Views
312
MARZIOTULLIO
M
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
280
Moley84
M
S
  • Solved
SUM column based on criteria of two other columns
2
Replies
10
Views
429
Fluff
Fluff

Forum statistics

Threads
1,224,514
Messages
6,179,223
Members
452,896
Latest member
IGT

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