Sum only certain cells

[Guest]

In row 5 I need a total of colums b through f multiplied by colum a unless
the cell contains text which in this case is "N/A"

example for column b

sumproduct(b1:b4,a1:a4) Unless the cell contains text which is "N/A".

So all of the columns in line 5 would have a value except for column f which
I need an "N/A".




A B C D E F

1 10% 15.58 38.50 26.68 9.43 N/A

2 10% 13.51 36.03 24.85 6.41 3.34

3 4% 8.41 28.66 20.26 4.86 N/A

4 4% 3.67 1. 36 15.75 9.89 9.2

5

 

[Biff]

Hi!

Try something like this:

=IF(COUNT(B1:B4)<4,"N/A",SUMPRODUCT(B1:B4,$A1:$A4))

Copy across as needed.

Biff

 

[Guest]

Biff,

This formula gives me a count of the cells that do not contain "N/A"

Any suggestions

 

[Biff]

This formula gives me a count of the cells that do not contain "N/A"

Used in this form:

SUMPRODUCT(B1:B4,$A1:$A4)

Sumproduct cannot count. It multiplies both arrays together and then sums
the result of the multiplication.

Based on your posted sample data the formula works:

=IF(COUNT(B1:B4)<4,"N/A",SUMPRODUCT(B1:B4,$A1:$A4))

See this screencap:

http://img49.imageshack.us/img49/2671/sump7ir.jpg

Biff