Friday, April 18, 2014
Using SUMPRODUCT to count
Lets look at the example below,
In column C dates and column D the error happened on the day with possible multiple entries.
The requirement is to calculate the number of times error5 occurred after 5/5/2012 and before 5/15/2012. (answer is 5).
Lets put the two dates in A1 and A2. The formula will be
=SUMPRODUCT((C1:C22>A1)*(C1:C22<A2)*(D1:D22="error5"))
If you do not want enter dates in cells the formula will be
=SUMPRODUCT(($C$1:$C$22>DATE(2012,5,5))*($C$1:$C$22<DATE(2012,5,15))*($D$1:$D$22="error5"))
Love to have your comments....
alternative link download
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.