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....

How to use SUMIFS

In an earlier post  I discussed about using CONCATENATE and SUMIF to sum with two conditions. This can be done using the new SUMIFS command.

lets look at the following example, if I need to know the quantity produced by machine 9 on date 4th Jan 2011.

Use formula

=SUMIFS(H:H,F:F,DATE(2011,1,4),G:G,9)

Done.

How to use SUMIF continued

Prerequisite 

Please refer to Microsoft help for Syntax.
=SUMIF(range,criteria,sum range)

Possible Usage

SUMIF is a versatile formula and can be used in different needs.

Example is using SUMIF in two column of data.

Need to calculate the quantity produced by machine 3.

The steps (How to do)

1. type =SUMIF(D:D,3,E:E) in B2

Other option is to type the machine number in one cell and use formula. For example =SUMIF(D:D,A2,E:E)
The formula can be used to calculate the quantity produced by every machine.

DONE

How to use SUMIF

Prerequisite 

Please refer to Microsoft help for Syntax.
=SUMIF(range,criteria,sum range)

Possible Usage

SUMIF is a versatile formula and can be used in different needs.

First example is using SUMIF in one column of data.

example

-5
10
-7
20
0.5
30
20
0.3
0.5
0.6
-10
10
20
-10

Need to add the values which are
1. less than zero
2. more than zero
3. in between 0 and 1

The steps (How to do)

1. type =SUMIF(A:A,"<0",A:A) in C1

2.  type =SUMIF(A:A,">0",A:A) in C2

3. Type =SUMIF(A:A,"<1",A:A)-SUMIF(A:A,"<0",A:A) or =SUMIF(A:A,"<1",A:A)-C1

Done

How to use CONCATENATE and SUMIF together

Prerequisite 

Know how to use "CONCATENATE" and "SUMIF" individually. Please refer to Microsoft help for 

Syntax. 

What is concatenation means, please read here, http://en.wikipedia.org/wiki/Concatenation.
Simply put join two or more words.


Simple Example 

Lets consider a simple scenario,

Data given (raw data) : quantity produced by different machines on different days

Data needed : mapping (matrix) qty produced by different machines on different days.

The steps (How to do)

Stap 1: Create a new column to join date and machine using "Concatenate".

  

Note I am using  "-" to make sure no duplicates.

the formula in column (refer to the image) is  =CONCATENATE(N3,"-",O3).

Make sure the formula is dragged to all the way to the bottom of the table.

Step 2: create the matrix 

Manufacturing date in the column and machines in the raw (it can be done vise-verse also)

Step 3: formula to calculate the total quantity produced by each machines on different days.

Use sumif to do the calculation. Please note the range column and sum_range column are different. Lets start in cell B3.


range    Required. Column M (where the concatenate values are)
criteria    Required. CONCATENATE($A3,"-",B$2)
sum_range     Required . Column P

Let's focus on CONCATENATE($A3,"-",B$2). This formula is the same as in column M. The main difference in freezing column and raw values. $A3 is column freeze and  B$2 is raw freeze. The freezing allow us to drag the formula.

Step 4: Drag the formula to fill up the entire table

DONE

Step 5: Check and Balance

Always good to do a check on the accuracy.

In this case SUM(B3:J12) sould be equal to SUM(P:P)




This is my first post in this blog let me know your thoughts.