Formula Breakdown for Understanding

    Hey guys!


    I am using a formula that looks at a range of cells and counts the number of cells with the date greater than todays date. Now I used the SUMPRODUCT formula that has been posted many times, but I like to have an understanding of exactly what the formula is doing. When I see SUMPRODUCT, I am thinking that something is being added, but I just can't get it to click in my mind. Can someone who has a few moments, just explain what each part of the formula is doing please, or just a summary of how SUMPRODUCT works?


    I modified it slightly for my use, but the gist of it is the same.


    Code
    =SUMPRODUCT(($I$50:$I$58>TODAY())*($I$50:$I$58="")+($I$50:$I$58>=TODAY()))&" Active"


    Thanks a bunch!

    Re: Formula Breakdown for Understanding


    I hear you. I struggle with this one as well but like a car, I'm just thankful it runs without me know how the engine works. :?


    Don't know if this link will help: http://www.ozgrid.com/Excel/sum-if.htm

    Barbara - aka The Cat Lady :cat:


    Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.

    Re: Formula Breakdown for Understanding


    Thanks for the Link, it did shed some light on it, but on another point it raised another question. My Current formula:


    Code
    =SUMPRODUCT(($I$50:$I$58>=TODAY())*($I$50:$I$58="")+($I$50:$I$58>=TODAY()))&" Active"


    Now basically all the first two parts are doing is coming up with a 0 value then adding the initial value. Why do it this long way instead of just doing


    Code
    =SUMPRODUCT((0)+(I50:I58>=TODAY()))&" Active"


    After testing it, it works perfectly, so why the long way, am I missing something? This is what is confusing me.


    Thanks again!

    Re: Formula Breakdown for Understanding


    Hmmmmmm ... the first part of your SUMPRODUCT does look like it will always produce zero. So your revision is probably ok. But I wonder what the first part was intended to do. Is the formula producing the result that should be produced in all cases?


    For some more info on SUMPRODUCT, have a look at the SUMPRODUCT sample file at:
    http://members.cox.net/rtcsolu…SUMPRODUCT%20Examples.xls

    Re: Formula Breakdown for Understanding


    Thomach, Awsome Link
    Using that link I was able to modify my Code to


    Code
    =SUMPRODUCT(($I$50:$I$58>=TODAY())*1)&" Active"


    So I think I am starting to understand it now. The SumProduct can be used to count Arrays, and in my formula the "*1" part must be the way it turns it into a number value, so this formula works as well, and is much simpler than the original.

Participate now!

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

Register Yourself Login