SUM HELP PLZ

[Lou Aiello]

I have a row of numbers where I need to total those with header A only if
some number is in B. example

A B A B A B A B
7 3 2 4 1 5

i..e.
a1="A",b1="B",c1="A",d1="B",e1="A",f1="B",g1="A",h1="B"
a2=7, b2=3, c2=2, d2="",e2=4, f2=1, g2=5, h2=""

The total should be 7 + 4 = 11 (because there's a 3 for the 7 and 1 for
the 4)

I can do this with something like
=IF(LEN(B2)>0,A2)+IF(LEN(D2)>0,C2)+IF(LEN(F2)>0,E2)+IF(LEN(H2)>0,G2)
But I have 50 columns and sense there is a more elegant solution. I tried
some SUMPRODUCT variations but can't seem to get the exclusion.

Any help would be appreciated.

 

[Dave Peterson]

How about:
=SUMPRODUCT((A2:G2),--(A1:G1="A"),--(B2:H2<>""))

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

 

[Dave Peterson]

Ps. Notice those ranges. One starts in column B and two start in column A.

 

[Lou Aiello]

That's it! Thanks.

Ps. Notice those ranges. One starts in column B and two start in column
A.