Find solution to #VALUE! error message in SUMPRODUCT

    Hello


    I have a macro enabled workbook with two worksheets and a #VALUE! error message appears in one column of formulae but not in another column that has the same formulae. To explain how the workbook works, basically one enters a start date (Sheet Availability D1) and a end date (Sheet Availability D2) for a 7 day period and then see the price for those 7 nights in Sheet A cells L13 and Y13 for two separate letting units.


    I've attached a copy of the workbook: the issue appears in Worksheet A cells U7:U16 (encircled in a red box) using a SUMPRODUCT function. As far as I can see, the same formulae are used in Worksheet A cells H7:H16 (encircled in a blue box) and return a result without the error message.


    In addition, when I look at a copy of the workbook, saved elsewhere, with the same formulae, both U7:U16 and H7:H16 work fine without an error message.


    Can anyone explain what's going wrong please ?


    Thanks

    Re: Find solution to #VALUE! error message in SUMPRODUCT


    There is an "h" in Z252. This is causing the error, since SUMPRODUCT is looking for numbers only to sum.


    Is the "h" an "accident"?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Find solution to #VALUE! error message in SUMPRODUCT


    Alternatively you can replace Sumproduct with Sumifs like this:


    [COLOR="#0000FF"]=IF(SUMIFS(Z$19:Z$446,P$19:P$446,">="&Availability!$D$1,P$19:P$446,"<" &Availability!$D$2)<=0,0,(Q8-(Q8*5)))[/COLOR]


    which will ignore the "h".. but then you won't know that it was entered possibly "accidentally".

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: Find solution to #VALUE! error message in SUMPRODUCT


    Thanks ever so much NBVC, I feel such a wally as I thought I'd checked "simple" things like that but I obviously missed it.


    It's been puzzling me for hours and I'm just so sorry there's no "technical learning" so to speak, to come from this for anyone else but I guess it reminds us all to check for bugs.


    I've added to your reputation and want to say a huge thank you.

    Re: Find solution to #VALUE! error message in SUMPRODUCT


    You're welcome. And thanks for the rep :)

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login