I have a simple array formula in "sheet2" that pulls data from "sheet1" in the range of -1 to 1. It looks like this:
=SUM((Sheet2!$B10=Sheet1!$A$12:$A$13)*(Sheet2!C$9=Sheet1!$B$11:$G$11)*(Sheet1!$B$12:$G$13))
Every month, more data is added, but I want to be able to drag the formula for the rest of the year even though the data for the next 4 months does not exist yet.
The problem is my array formula returns empty cells as 0. I tried using an IF statement to put zeros as NA(), but some of my data includes zeros and I want those zeros to show up.
Finally, the reason I want zeros to show up, but not empty cell zeros is because I have the array data automatically charted on a line graph and I don't want the line dropping to zero just because the data is nonexistent.
Thanks in advance for any help!
Bookmarks