SUMPRODUCT Returns N/A Error

    Hi all -


    I have sumproduct formula that returns #N/A error and I'm not sure why
    =SUMPRODUCT((YTD!$A$2:$A$2599=B$10)*(YTD!$P$2:$P$2599=$Q19)*(YTD!$E$2:$E$2599))


    If I narrow the range from $939 to $1093 then the formula works.
    But that is only evaluating 1 unit of 10
    As I add worksheets to the workbook and update the unit numbers in B10, the entire range needs to be evaluated for matching criteria.


    Any ideas?
    Sorry, workbook is way too large to upload.
    TIA
    -markc

    Re: Array Formula Returns Error


    Thanks
    It is not committed with (ctrl+shift+enter)
    How can I change the title?


    Thanks
    -markc

    Re: SUMPRODUCT Formula Returns Error


    Thanks parsnip
    No.
    I scrolled through all data and used Edit..Go To...Special...Formulas...Errors
    "No Errors Were Found"


    There are no blank cells in the range.
    Thanks
    -markc

    Re: SUMPRODUCT Formula Returns Error


    Try substituting the formula with an array formula (Ctrl+Shift+enter):


    =SUM(IF(YTD!$A$2:$A$2599=B$10,IF(YTD!$P$2:$P$2599=$Q19,YTD!$E$2:$E$2599)))


    If this still errors then check the E column with:


    =SUMIF(E2:E2599,"#N/A")


    Richard

    Re: SUMPRODUCT Formula Returns Error


    Thanks
    The array formula does return the expected result.
    Still curious why the sumproduct does not work though?


    Thanks
    -markc

    Re: SUMPRODUCT Formula Returns Error


    Thanks
    Checked again
    There is #N/A in col P
    I guess it did not show in the Go To Special....Error because at some point I pasted the lookup formula as a value


    Thanks for your help!
    -markc

Participate now!

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

Register Yourself Login