SUMIFS or SUMPRODUCT both returning #VALUE error

    • New

    Hi Folks


    I've tried both SUMIFS and SUMPRODUCT to take the value in column B on sheet 3 if the "Section" in column A here appears in the section column A:A and the date in row B here is in row 1 on sheet 1 but I keep getting a #VALUE error... :(


    I think it's perhaps because I am mixing requests from both Rows and Columns???


    Any ideas please???


    Rows_and_Columns.xlsx


    As always, heartfelt thanks...


    Kind regards


    DezB

    • New

    You should never use whole column references in SUMPRODUCT formulas. Not only is it hugely inefficient but here it also means that you are including a header row in the multiplication, and you can't multiply text.


    This will work:


    =SUMPRODUCT(Sheet3!$B$2:$B$18*(Sheet3!$A$2:$A$18=A4)*(Sheet1!B1:G1=B2))


    As an aside, it's really not a great layout to have formulas that rely on two different sheets being in the same order.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • New

    Hi,


    Please explain your underlying logic .... and show your expected results in Sheet 2 ... :)


    Edit : Good to see that ... Rory has decoded your objective !!! ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Edited once, last by Carim ().

Participate now!

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

Register Yourself Login