Need help with $Value! error

[EssKayKay]

I have a problem I have no idea why. I have a formula in a range (S33:S##) that returns the sum of another range. It works fine until I enter something into various cells that apparently are affecting this answer. However, I do not see where or why there is the conflict.

In many instances if the number(s) enter are small it works fine. However as the numbers the formula returns a $Value! error.
I receive this formula from a generous member here who I am very grateful for; however, I do not know who or when. The formula producing the error is this:

=IF(SUMPRODUCT(--(YEAR($C$32:$C$365)=R37),$Q$32:$Q$365)>0,SUMPRODUCT(--(YEAR($C$32:$C$365)=R37),$Q$32:$Q$365),"")

I suspect this will be close to impossible to comment on without seeing the entire worksheet which is quite complicated as I am not a programmer and not even much of a hacker – but I try.
To view the spreadsheet – Dropbox
Please note: This is just an example of what’s happening. It is by no means working as expected but if I could get this resolved would be so helpful.

Any suggestions would be appreciated and thank you for viewing,
Steve K.

 

[Michael M]

It appears I can't open this file.
Can you upload it as an XLS ??

 

[EssKayKay]

It appears I can't open this file.
Can you upload it as an XLS ??

Thanks for viewing Michael. I have changed the format to an xls file. There are numerous macros etc. some of the operation may not function as expected. This is a rough draft and not yet ready for prime time.

At your convenience could you take a look and see if anything pops out at you. I realize this is a very confusing worksheet and as such may not be easily to decipher.

file: Dropbox

Again, my thanks,
Steve K.

 

[EssKayKay]

It appears I can't open this file.
Can you upload it as an XLS ??

First off I made a mistake. It is not $Value! its #VALUE! - so sorry.

I uploaded the revised file to Dropbox. (see above). Regarding the #VALUE! error - as example of what I am talking about, if you enter 100 (or some other lager number) in cell K6 you will see the #VALUE! appear in the S33:S... range.

I'd like to change the title here to more accurately describe my problem but I guess we can't do that.

 

[Peter_SSs]

You have formula in column C that in rows 335 & down return "". Your formula in column S tries to get the year from column C. "" does not have a year so it cannot calculate that value, hence the error.
Are you still using Excel 2007?

 

[EssKayKay]

You have formula in column C that in rows 335 & down return "". Your formula in column S tries to get the year from column C. "" does not have a year so it cannot calculate that value, hence the error.
Are you still using Excel 2007?

Thanks Peter for your concern. Yes I'm still running v2007. I think I may know how to fix this. I'll work on it and if I have more issues I'm sure I'll be back.

Once again, thanks again for your keen eye and suggestion - much appreciated,
Steve K.