Sum function in Array function not working

    Hi All -


    I'm having an issue with an array formula. Basically what I have set up is the following formula =SUM(IF(($B$2:$B$243>=E15)*($B$2:$B$243<=F15),$C$2:$C$243)).


    This formula will look at the specific criteria and determine how many clicks are within the "start" and "end" range criteria that I have predetermined. The total number of clicks is just over 20K but after running the array I am getting 26,558 clicks which doesn't make any sense.


    Could someone take a look at the attached spreadsheet and let me know what I am doing incorrectly?


    Thanks

    Re: Sum function in Array function not working


    You are double counting some of them because you use >= and <= in your criteria. So for example a value of 1 in column B counts for 0-1 and 1-2. Try using:
    =SUMPRODUCT(($B$2:$B$243>=E15)*($B$2:$B$243<F15),$C$2:$C$243)
    in H15 and copy down. (No need to array enter) This will only include the upper value in the next band.

    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

    Re: Sum function in Array function not working


    Rory! Thanks so much!


    You have saved me a great deal of time when the issue was right in front of me. Cheers!

    Re: Sum function in Array function not working


    I would use =SUMPRODUCT(--($B$2:$B$243>=E15),--($B$2:$B$243<=F15),$C$2:$C$243)
    Edit - Sorry Rory - I cross posted after you answered.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




Participate now!

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

Register Yourself Login