Issues with using the SUMPRODUCT function

[Bones97698]

Hello,

I am having a pretty large issue with using the SUMPRODUCT function in regards to counting values in a column that are greater than 0. I have to use the following formula due to setting up autofilters on the sheet, so the use of a function that corresponds with this is necessary. I am using the following function to count values that are greater than 0:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(Z12:Z7566,ROW(Z12:Z7566)-ROW(Z12),0,1)),--(Z12:Z7566>0))

It's pretty nasty, but the odd thing about this formula is that when I generate my sheet it comes up with a #VALUE! error...but when I double click and press enter it calculates just fine. It's extremely odd and frustrating.

I've tried recalculating the sheet after the formulas have been set and this does not solve the issue (both in excel and also setting it in java). I think that the issue may center around the data that is being counted by the above function are also SUM functions. Could the issue potentially be the fact that the SUMPRODUCT function calculates cells that haven't been calculated yet by the SUM function? Or am I missing something else or should I be using a different formula completely? Suggestions are appreciated! Thanks.

[NBVC]

What do you mean by "when I generate my sheet"?

Are you copying new data over old data?

Can you supply a workbook sample showing this happening?

[Bones97698]

The sheet is being generated in a java application. It is coded and written to do a multitude of things. That being said, my question may not be able to be answered because of that, but here is an example of what i'm working with. Thanks for your help.