Adding two cells not working

[DEER30]

Hey yall, This is extremely basic and something I have done a thousand times, but for some reason, now when I am adding two cells (which have numeric values) the total cell is showing 0. Any ideas why?

Example Cells to be added have formulas: Cell A1 =SUMPRODUCT(H9:H1000,(A9:A1000<=9.14)*(A9:A1000>=9.08)*(D9:D1000="Y")) which results in a value of 5.
Cell A2 =SUMPRODUCT(I9:I1000,(A9:A1000<=9.14)*(A9:A1000>=9.08)*(D9:D1000="Y")) resulting in 13

But Cell A3 (=SUM(A1:A2)) reads 0.

Suggestions?

Thanks

[6StringJazzer]

I set this up using your formulas, but I had to provide the data myself. When I get 5 and 13, my A3 formula gives 18. So please attach your file for a more thorough diagnosis.

[DEER30]

does the cell format matter? since I have cells with text and cells with numeric values, would those affect the formula's performance?

[6StringJazzer]

Format doesn't matter but content does. (Format is just how it's displayed.) If the results of the formulas in A1 and A2 are text rather than numeric, even if they look like numbers, then the result in A3 will be 0. Instead of using SUM, try this

=A1+A2

That will force a conversion of text to numbers to be able to perform the addition. SUM does not force a conversion, it just ignores text.

However, I am still not seeing how your formulas can have a text result. If my formula does not help then I can't do anything else without your file.

[DEER30]

The problem appears to be that the formulas within the cells I was adding (SUMPRODUCT), had ranges of analysis too large to compute (9-1000). Once I reduced the range (300-500) everything works fine.

[6StringJazzer]

I believe there is no range size limit for SUMPRODUCT. I still think you have an issue in the data itself. Reducing the range may be omitting the data causing the issue.