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 COUNTIFS

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

Lets look at the following example:

I need to know the number of times machine 9 was used on  4th Jan 2011.

Use formula,

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

You can use up to 127 criteria

DONE.

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 avoid BLANKS / errors in CHARTS

Prerequisite 

Able to use/create charts in Excel. Know how to use "ISERROR" formula. Please refer to Microsoft help for Syntax.

Possible Usage

To reflect a clean and accurate in formation in charts.

For example, refer to the table below.

If we plot Reject Percentage Vs Date we will get,

First the chart is not accurate as there was no production in certain days and reject rate cannot be considered "Zero".

Second the chart is not tidy. 

The steps (How to do)

1. type =IF(ISERROR(C2/B2)=TRUE,#N/A,C2/B2) in D2

2. Drag or Copy/Paste all the necessary cells

3. We will get a clean and accurate chart

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 DATE, RIGHT and LEFT formulas to create date from unique IDs

Prerequisite 

Know how to use "DATE", "RIGHT" and "LEFT" formulas individually.
Please refer to Microsoft help for Syntax.

Possible Usage

In the earlier post we talked about how to create unique IDs using data code or time stamp. Here we will see how to do the reverse. This kind of situation does arises in Operation or Manufacturing environments.

The steps (How to do)

To create date we need year, month and day.

1. Type =LEFT(F2,4). Drag or copy paste the formula all the way (to cover the all the IDs). This will be the year.

2. Type =RIGHT(LEFT(F2,6),2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the month.

3. Type =RIGHT(LEFT(F2,8),2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the day.

4. Type =DATE(E2,D2,C2) Drag or copy paste the formula all the way (to cover the all the IDs). This will be the date.

DONE.

All comments are welcome.

How to use CONCATENATE, YEAR, MONTH, DAY and LEN formulas to create unique IDs

Prerequisite 

Know how to use "CONCATENATE", "YEAR", "MONTH" "LEN" and  "DAY" formulas 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.

Possible Usage

To create unique IDs using data code or time stamp. This is more applicable to operation or manufacturing environment.

example: create IDs using dates.

The steps (How to do)

1. type =YEAR(A2) in cell B2

2. type  =MONTH(A2) in cell C2

3. type =DAY(A2) in cell D2

4. Add running numbers in column E. Use "text" format to make sure zeros appear in front.

5. type =concatenate(B2,C2,D2,E2). Drag or copy paste the formula all the way (to cover the all the dates)

Note that the ids are not of the same length. 

To avoid you can use IF and LEN as described below.

2a. type =IF(LEN(MONTH(A2))=1,CONCATENATE("0",MONTH(A2)),MONTH(A2)) in column C 

3a. type =IF(LEN(DAY(A2))=1,CONCATENATE("0",DAY(A2)),DAY(A2)) in Column D

The result will be as follows

Here we go nice and clean IDs.

Done.

All comments are welcome.