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!

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

"Damien" wrote:

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!

That did it. Thanks for the help!

"Elkar" wrote:

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

"Damien" wrote:

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!