Showing posts with label sumproduct. Show all posts
Showing posts with label sumproduct. Show all posts
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....
Subscribe to:
Posts (Atom)