Can Someone Explain how this formula works?

The following formula is used to count the number of dates in a column of which the date is later than the date stored in cell D1.

I'm just looking for pointers on how it works.


=SUMPRODUCT((C4:C25>D1)*(C4:C25<>"N/A"))

Many Thanks

Yes, here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

and

http://www.excelhero.com/blog/2010/0...umproduct.html

Hi,

In my opinion a straightforward =COUNTIF(C4:C25,"<>N/A") would have been preferable.

SUMPRODUCT was more extensively used prior to XL 2007 which ushered in SUMIFS() and COUNTIFS() which largely did away with the need for the SumProduct array formula.

It works by evaluating each row from C4:C5 in the two internal sets of parentheses. So for instance if C4 > D1 then a value of 1 is returned, and if not zero. Similarly if C4 <> "N/A" then that too will result in a 1. Therefore the effect of multiplying 1 x 1 for C4 gives the value 1. If say on C5 the two results are 1 & 0 then C5 will evaluate to 0. You can probably see therefore that the sum of all these individual row C results will give you the count of how many rows in C4:C25 meet both criteria.

Sorry,

I mentioned COUNTIFS() plural and then didn't use it. Instead

=COUNTIFS(C4:C25,"<>N/A",C4:C25,">"&D1)