Using SUMPRODUCT to count

Let's 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....

SUMIF, SUMIFS and SUMPRODUCT

Let's look at the example below.

Zone	Shift	No of  Machines	Output per machine
Zone 1	Shift A	15	15
Zone 2	Shift A	15	15
Zone 1	Shift B	15	20
Zone 2	Shift B	15	20
Zone 3	Shift A	15	20
Zone 1	Shift B	20	20
Zone 3	Shift B	20	15
Zone 3	Shift B	20	20
Zone 1	Shift A	20	20
Zone 2	Shift B	15	20

The requirement is to calculate the output for Zone 1 for Shift B.

SUMIF

1. Using SUMIF would require two additional columns to be created.

2. One to CONCATENATE to Zone and Machine =CONCATENATE(I2,J2) and another to multiply column K and L =K2*L2

3. Then we can use ==SUMIF(M2:M11,"Zone 1Shift B",N2:N11)

SUMIFS

1. Using SUMIFS will require one additional column to be created.

2. Add a Column to multiply column K and L =K2*L2 (column M lo longer required).

3. Then we can use =SUMIFS(N2:N11,I2:I11,"Zone 1",J2:J11,"Shift B")

SUMPRODUCT

1. Using SUMPRODUCT will NOT require any more additional columns.

2. SUMPRODUCT is meant for "Multiplies corresponding components in the given arrays, and returns the sum of those products."

3. IF we use =SUMPRODUCT(I2:I11="Zone 1",J2:J11="Shift B",K2:K11,L2:L11) the answer will be "0". It is because the results of 

I2:I11="Zone 1" = {TRUE, FALSE,TRUE, FALSE,FALSE....}

J2:J11="Shift B" = {FALSE,FALSE,TRUE,TRUE,FALSE.....}

4. To change TRUE  into 1 and FALSE into 0, we need to use "--". 

=SUMPRODUCT(--(I2:I11="Zone 1"),--(J2:J11="Shift B"),K2:K11,L2:L11)

--(I2:I11="Zone 1") = {1;0;1;0;0;.....}
--(J2:J11="Shift B") = {0;0;1;1;0......}

Note: 

-(I2:I11="Zone 1") = {-1;0;-1;0;0;.....}
-(J2:J11="Shift B") = {0;0;-1;-1;0......}


5. Essentially the calculation is,

(1x0x15x15 + 0x0x15x15 + 1x1x15x20 +........)

6. This formula will give answer we need.

Love to have your comments....