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......}
--(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 +........)
ExpiresDefault access plus 1 year
Thanks for listing out these functions. This post is very useful
ReplyDeleteAdvanced microsoft excel training in chennai, nungambakkam
I love to use the SUMIF function because it's practical but until I understood it, it took a long time :) It's a pity that I didn't came across to your website earlier, namely it's brief and concise. I have learned to use the SUMIF function by the following workshop what I recommend for all excel beginner http://www.excel-aid.com/the-excel-sumif-function.html
ReplyDeleteSUMIF, SUMIFS, and SUMPRODUCT are powerful Excel functions that help you perform complex calculations with ease. With ModEngine2, you can optimize your data processing workflows, ensuring smooth integration and enhanced performance for these functions, making your analysis more efficient and reliable.
ReplyDelete