# 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

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