#DIV/0 Error, when there is NO "0" possible!

I hope that someone can help me. I am working on a large, monthly statistical analysis spreadsheet, where from month to month, the calculations are repeted. The "monthly data" ranges in size from 10,000 rows to 25,000+ rows. Then, there is a "Summary" sheet that generates my statistical data broken down by STATE. All named ranges in the following formula (and in entire spreadsheet) are 40,000 rows deep.

Here is the problem: The summary calculations work for some months, but not for others, and where it fails, there is one commonality, I get a #DIV/0 error. BUT... the formula that generates this errors is an array SUMPRODUCT formula... there is NO DIVISION INVOLVED! Here is the offending Formula and some explanation of the ranges called:

{=SUMPRODUCT((ST_1=$A2)*(XYZ_1>0)*(Use_1=1))}

where
ST_1 is an alpha field (40,000 rows) , with the 2-letter abreviation for a state
XYZ_1 is numerical data
Use_1 is a "1" or a "0" that is generated by the following:
=IF($Z20096>0.99,0,1)
This determintes whether or not the "data" is "useable"

The SUMPRODUCT should return a COUNT of the ROWS that contain USEABLE data. NOW..... Whenever the "Use_1" named field gets called, I get a #DIV/0 error (the Use_1 values is only in the Numerator)

All of my formulas are working fine on the "raw data" or monthly data sheets, but when in my statistical SUMMARY sheet (where the SUMPRODUCT and "Use_1" condition is called, I get the #DIV/0 error on 2 of 5 months tabulated.

If I have thoroughly confused you, I will try to be more specific... but let me know!

Thanks
Walker

I think that one of the cells you are including in the SUMPRODUCT has a #DIV/0 error, and that makes the SUMPRODUCT report #Div/0.

Hello Walker,

Sorry, doesn't help with your specific problem but note that your SUMPRODUCT formula doesn't need to be "array entered", it works with just ENTER

Thanks, but unfortunately, the fields/columns I am calling in the SUMPRODUCT are all pasted values (formatted as "general" and values only) from another spreadsheet. I originally had it all in one spreadsheet, but it was taking up to 45 minutes to recalculate the entire spreadsheet, so I split it in two. I have also tried reformatting the appropriate cells as "general" or "number" where appropriate, but to no avail. What is really confusing me is that it works for some "months" but not for others. There appears to be some attribute in the RAW data that is triggering this, because JANUARY, MARCH and MAY work fine, APR and JUNE are failing. Here is why I think it is the raw data: When I paste in data from JAN, it works. When I past in data from APR, it fails. At the same time, I can run other formlas that rely on the data in these fields without having a problem! I'm confounded!

Thanks for your help!