Array Problem - Ranges

I am somewhat new to arrays and I have run into problem. Here is what I am working on.

I have an array set up to SUM up dollar values from Column B based on what Column A contains. I know how to do this; however, my problem is with the range that I use for each column.

I plan to use this for several different applications and each application has a different number of rows when I import the data.

For example, let's say that I have numbers in column B from row 1 to row 35 BUT I have my ARRAY formula set up to add numbers (based on arguement in Column A) from B1 to B1000.

This should be easy enough; however, I have found that the application where I get the data sends BLANK cells as opposed to "0" if a row is not used. Therefore, I get an error on the formula.

I have found that if I go back and add zeros to all the blank cells from row 36 to row 1000, my formula works; however, this could become very tedious. Is there something else that I can do so that my array formula would work with the large range even if there are BLANK cells?

My array formula . . .
{=SUM((A1:A1000="Constraint")*B1:B1000)}

Any help is appreciated

Try the following...

=SUM(IF((A1:A1000="Constraint")*(ISNUMBER(B1:B1000)),B1:B1000))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

Originally Posted by TonyRowland

I am somewhat new to arrays and I have run into problem. Here is what I am working on.

I have an array set up to SUM up dollar values from Column B based on what Column A contains. I know how to do this; however, my problem is with the range that I use for each column.

I plan to use this for several different applications and each application has a different number of rows when I import the data.

For example, let's say that I have numbers in column B from row 1 to row 35 BUT I have my ARRAY formula set up to add numbers (based on arguement in Column A) from B1 to B1000.

This should be easy enough; however, I have found that the application where I get the data sends BLANK cells as opposed to "0" if a row is not used. Therefore, I get an error on the formula.

I have found that if I go back and add zeros to all the blank cells from row 36 to row 1000, my formula works; however, this could become very tedious. Is there something else that I can do so that my array formula would work with the large range even if there are BLANK cells?

My array formula . . .
{=SUM((A1:A1000="Constraint")*B1:B1000)}

Any help is appreciated