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.

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

    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:

    =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

    =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:…SUMPRODUCT%20Examples.xls

  • Re: Formula Breakdown for Understanding

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

    =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!