Conditionally ignoring certain cells in columnar calculations

[Guest]

It's probably easier if I ask this using an example, so please reference this
sample:

A B

1 0.3 0.2
2 0.4 0.3
3 0.5 0.4
4 # N/A N/A 0.5

I use a Bloomberg add-in to automatically load data into Excel. If for some
reason this data is not available, the add-in will return a value such as the
one seen in cell A4.

What I'm trying to do is sum these numbers and calculate the percentage
change from the sum of column B to the sum of column A. However, calculating
the percentage change in three quarters' worth of data over four is useless,
so I'm trying to find a way (SUMIF?) to exclude cell B4 from the sum
calculation of column B if the adjacent cell (A4) is blank or contains an
error code like the one shown above. Is this possible?

Thanks so much for the help!

 

[Guest]

It's not pretty, but this should get you the sum of column B that you want:

=SUMPRODUCT(--NOT(ISERROR(A1:A4)),--NOT(ISBLANK(A1:A4)),B1:B4)

HTH,
Elkar

 

[Guest]

That did it. Thanks for the help!

It's not pretty, but this should get you the sum of column B that you want:

=SUMPRODUCT(--NOT(ISERROR(A1:A4)),--NOT(ISBLANK(A1:A4)),B1:B4)

HTH,
Elkar