sum if question

[verygreen]

hi,

i need to sum a1:a100 and b1:b100 but only when a and b both have a value, so if a3 has a value but b3 doesn't, or vice versa, don't include a or b in the summation. your help will be much appreciated.

verygreen

 

[Andrew Poulsom]

Try:

=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(ISNUMBER(B1:B100)),((A1:A100)+(B1:B100)))

 

[verygreen]

thanks andrew, but it's not working. i think i might need a sumif type of function. i'll keep searching. in the mean time, i'd appreciate any suggestions from anyone. ta.

 

[verygreen]

here's my question,

A B
10 5
9
8
7 10

Total 17 15

i want to sum column A and B but only where both columns have a number in the same row. any suggestions? thanks in advance.

 

[Andrew Poulsom]

With this data in the range A1:B4:

<TABLE style="WIDTH: 96pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=128 border=0 x:str><COLGROUP><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=64 height=17 x:num>10</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right width=64 x:num>5</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>9</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>8</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num>7</TD><TD class=xl22 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>10</TD></TR></TBODY></TABLE>

the formula I posted returns 32 (17 + 15). If you want to sum the individual columns:

=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(ISNUMBER(B1:B100)),A1:A100)
=SUMPRODUCT(--(ISNUMBER(A1:A100)),--(ISNUMBER(B1:B100)),B1:B100)

which return 17 and 15 respectively.

 

[verygreen]

hey that's brilliant! thanks a million, andrew