Getting #VALUE with SUMPRODUCT formula

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi all,


    Recently, I have problems with getting my SUMPRODUCT formula to work.


    Here is my formula:
    =SUMPRODUCT((YEAR(J3:J1186)=N3)*(K3:K1186))


    I am using this formula to help me sum all the values in a particular year. I had made sure the ranges have the same dimensions and that they are all of the same format.


    [SIZE=13px]J3:J1186 is the range of the column containing the dates (all are in the dd/m/yyyy format, an example of a date is 15/1/2018).
    N3 is the cell containing the year I want (eg 2018).
    K3:K1186 is the range of values that I want to sum based on the year of the date column (J3:J1186). All of the values are in the number format. This range of values have 0 inside, which is what I am suspecting to cause the error.


    Is there anyone that could help me with this error?


    Thank you and have a nice day


    ~Hestia[/SIZE]

    Hello,


    You could test following :


    Code
    =SUMPRODUCT(--(YEAR(J3:J1186)=N3)*(K3:K1186))


    Hope this will help

    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 :)

    Hi Carim,


    I had tried that formula but it doesn't work as well. Looks like it is not because of the 0s in my data set.


    ~ Hestia

    Also I forgot to add on that my values are drawn using an Index and Match formula from another sheet in the same workbook. Is that a possible reason why it affects the formula?

    Hi again,


    Could you kindly attach a tiny sample file ... both to understand all your constraints and to perform a couple of tests ... :smile:

    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 :)

    Hello,


    Thanks for your file ...


    All your Sumproduct formulas located in Column E are correct ..


    and do not produce any error ...


    Where is exactly your problem ?

    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 :)

    Hi Carim,


    Actually I did not have any errors with the formula when I opened the file on a newer version of excel on my PC. But when I opened the file on my old laptop with old excel, it just could not work haha.


    Is it possible that old excel could not read formulas with such huge arrays, or is it because my computer disk space is little?


    The picture attached shows what I am seeing when opening the file on my old excel.


    Thanks for all the help so far!


    ~ Hestia

    Hello,


    How old is your old computer ... ??? :lol::lol::lol:


    In fact, the sumproduct function is an Array function ...(without CSE) ...


    So it could very well be ... that given the sheer size of your matrix ...


    your old computer could eat up all of its available RAM ....:angrypc:


    Let me confirm to you : your Sumproduct formula is correct ... :sing:

    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 :)

    Hi Carim,


    Haha it is not that old :) Only 3 if i am not wrong.


    And yeah the size of the array is pretty big, which is probably the reason why my old laptop could not handle it.


    Though i really do need that large size, due to having a huge set of historicals, which will constantly be updated.


    Thank you for all your help though XD


    ~ Hestia

    Is it alright if I make a different question that is still regarding the SUMPRODUCT formula?


    Right now, I would like to populate all the other blank cells in volume row with my index and match formula, then hide it with iferror("index formula here", ""). However, this causes my sumproduct formula to not work again. Any ways that i could bypass this error and just sum up all the year values i want and ignore the rest?


    Thanks :)

    Hello,


    To answer your latest question ...


    True Blank cells will not interfere with your Sumproduct formula ...


    However, if, with a formula, you are creating the ' visual illusion of Blank / Empty ' ... for example with ""


    would recommend to replace "" by the number 0


    Hope this will help

    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 :)

    Hi Carim,


    Actually, I was trying to avoid the use of iferror("index formula here", 0), as all of these values are going to be displayed in a dynamic chart later on. I am not sure if these will affect the creation of the chart, but for now this seems like the only solution.


    Thanks for your help once again!


    ~ Hestia

    Glad you could fix your problem ... :smile:


    Let me reassure you ... regarding Charts ... there are also some tricks ... .wink:

    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 :)

    Hey Carim,
    Hahahaha, tricks huh? Alright now i know who to find if i have any problems with those charts :lol: :congrats:


    Thanks for all the help once again :)


    ~ Hestia

    Hello Hestia,


    Feel free to come back to the Forum ... with your next challenges ...


    :machineg:

    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 :)

Participate now!

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

Register Yourself Login