Sebaik-baiknya manusia, ialah yang berguna bagi yang lain.


Multiple Condition Tests

One of the most basic functions in any spreadsheet is to return an answer based upon some condition. This becomes especially useful when counting or summing based upon that condition. One condition is useful, but multiple conditions extend the functionality and flexibility, so that you can count say the number of items sold by part number AND by month. There are a number of ways that this can be achieved within Excel, but this paper is focussing on one particular function, the SUMPRODUCT function, which by creative use has evolved a flexibility undreamt of by its originators in Microsoft.

SUMPRODUCT is one of the most versatile functions provided in Excel. In its most basic form, SUMPRODUCT multiplies corresponding members in given arrays, and returns the sum of those products. This page discusses the classic use of SUMPRODUCT, how creativity and inbuilt flexibility has enabled it to evolve into a far more useful function, and explains some of the techniques being deployed. Finally, some examples of SUMPRODUCT show its versatility.

Standard Use of SUMPRODUCT
Evolving Use of SUMPRODUCT
Advantages of SUMPRODUCT

Standard Use of SUMPRODUCT

In it's classic form, SUMPRODUCT multiplies each value in one array by the corresponding value in another array, and returns the summed result. As an example, if cells A9:A11 contain the values 1,2,3 and B9:B11 contain 10,20,30, then


returns 140, or (1*10)+(2*20)+(3*30)=10+40+90=140.

This is a useful function, but nothing more than that. A further, more 'creative' use of SUMPRODUCT has evolved, and is still evolving, driven as far as I can see mainly by the regular contributors of the Microsoft Excel newsgroups. This has been a creative and productive process that has significantly increased the useability of SUMPRODUCT, but in a way that you will not find documented in Excel's Help.

Evolving Use of SUMPRODUCT

Within Excel, there are two very useful functions that support conditional counting and summing, namely COUNTIF and SUMIF. Very useful functions, but limited in that they can only evaluate a single test range, and only a single test condition. Multiple conditions are so useful to test ranges (say between two dates), and double tests (one array = A and another = B), and whilst this can be managed using array functions
=SUM(IF(test_A,IF(test_B, etc.,
this is somewhat unwieldy, and is an array formula. And there is a better way, using SUMPRODUCT.

Note that in this section, all formulae given are using the '*' (multiply) operator format, but this in itself is one of the biggest discussion points around the SUMPRODUCT function, one which is discussed below.

To understand how SUMPRODUCT can be used, first consider the following data.

1 Make Month Price
2 Ford June 7,500
3 Ford June 8,300
4 Ford May 6,873
5 Ford June 11,200
6 Renault June 13,200
7 Renault June 14,999
8 BMW June 17,500
9 BMW May 23,500
10 BMW June 18,000
Table 1.

We can easily count the number of Fords with
which returns 4.

Similalrly, it is straight-forward to get the value of Fords sold, using
which gives 33,873.

How do we get a count of how many Fords are sold in June, or the value of them? The number can be calculated with
which is an array formula so is committed with Ctrl-Shift-Enter, not just Enter. Similarly, the value is obtained with
also an array formula.

But as this page is about SUMPRODUCT, you would expect that we could use that function in this case, and we can. The solution for the number of Fords sold in June using this function is
The value is obtained with
In my view, this formula more readily shows what the objective is.

As a further extension of its use, we can use the '+' (plus) operator to count OR conditions, such as how many cars sold were either Fords, or were sold in June. This formula shows how

Although array formulae are mentioned here, they are not explained. For a detailed discussion, see Chip Pearson's Array Formulas web page.

So far, so good, in that we have a versatile function that can do any number of conditional tests, and has an inbuilt flexibility that provides extensibility. Its power is augmented when combined with other functions, such as can be found in the examples below.

Advantages of SUMPRODUCT

Multiple conditional tests are a major advantage of the SUMPRODUCT function as descibed above, but it has two other considerable advantages. The first is that it can function with closed workbooks, and the second is that the handling of text values can be tailored to the requirement.

In the case of another workbook, the SUMIF function can be used to calculate a value, such as in
=SUMIF('[Nowfal Rates.xls]RATES'!$K$11:$K$13,">1")

This is fine in itself, and the value remains if the other workbook is closed, but as soon as the sheet is re-calculated, the formula returns #VALUE. Similarly, if the formula is entered with the other workbook already closed, a #VALUE is immediately returned.

SUMPRODUCT, however, overcomes this problem. The formula
=SUMPRODUCT(--('[Nowfal Rates.xls]RATES'!$K$11:$K$13>1),--('[Nowfal Rates.xls]RATES'!$K$11:$K$13))
returns the same result, but it will still work when the other workbook is closed and the sheet is re-calculated, and can be initially entered referencing the closed workbook, without a #VALUE error.

The second major advantage is being able to handle text in numeric columns differently. Consider the follwoing dataset, as shown in Table 2.

1 Item Number
2 x 1
3 y 2
4 x 3
Table 2.

If we are looking at rows 1:4. we can see that we have a text value in B1 In this case it is simply a heading row, but the principle applies to a text value in any row.

Using SUMPRODUCT, we can either return an error, or ignore the text. This can be useful if we want to ignore errors, or if we want to trap the error (and presumably correct it later).

Errors will be returned if we use this version

To ignore errors, use this amended version which uses the double unary operator (see SUMPRODUCT Explained below for details)

But how does it work?


To understand how SUMPRODUCT works will help you to determine where to use it, how you can construct your formula, and thus how you can extend it.

Table 3. below shows an example data set that we will use. In this example, the problem is to find how many Fords with a category of "A" were sold. A9:A20 holds the make, B9:B20 has the category, and C9:C20 has the number sold. The formula to get this result is

The first part of the formula (A9:A20="Ford") checks the array of makes for a value of Ford. This returns an array of TRUE/FALSE, in this case it is

Similarly, the categories are checked for the vale A with (B9:B20="A"). Again, this returns an array of TRUE/FALSE, or

And finally, the numbers are not checked but taken as is, that is (C9:C20), which returns an array of numbers

So now we have three arrays, two of TRUE/FALSE values, one of numbers. This is showm in Table 4.

And this is where it gets interesting. SUMPRODUCT usually works on arrays of numbers, but we have arrays of TRUE/FALSE here as well as an array of numbers. By using the '*' (multiply) operator, we can get numeric values that can be summed. '*' has the effect of coercing these two arrays into a single array of 1/0 values. Multiplying TRUE by TRUE returns 1 (try it, enter =TRUE*TRUE in a cell and see the result), any other combination returns 0. Therefore, when both conditions are satisfied, we get a 1, whereas if any or both conditions are not satisfied, we get a 0. Multiplying the first array of TRUE/FALSE values by the second array of TRUE/FALSE values returns a composite array of 1/0 values, or
This new array of 1/0 values is then multiplied by the array of numbers sold to give another array of numbers sold that satisfy the two test conditions. SUMPRODUCT then sums the members of this array to give us a count.

Table 4. below shows the values that the conditional tests break down to before being acted upon by the '*' operator. Table 5. shows a virtual representation of those TRUE/FALSE values as their numerical equivalents of 1/0 and the individual multiplication results. From this, you should be able to see how SUMPRODUCT arrives at its result, namely 35.

9 Ford B 3
10 Vauxhall C 4
11 Ford A 2
12 Ford A 1
13 Ford D 4
14 Ford A 3
`5 Ford A 2
16 Renault A 8
17 Ford A 6
18 Ford A 8
19 Ford A 7
20 Ford A 6
Table 3.

9 TRUE * FALSE * 3
10 FALSE * FALSE * 4
11 TRUE * TRUE * 2
12 TRUE * TRUE * 1
13 TRUE * FALSE * 4
14 TRUE * TRUE * 3
15 TRUE * TRUE * 2
16 FALSE * TRUE * 8
17 TRUE * TRUE * 6
18 TRUE * TRUE * 8
19 TRUE * TRUE * 7
20 TRUE * TRUE * 6
Table 4.

9 1 * 0 * 3 0
10 0 * 0 * 4 0
11 1 * 1 * 2 2
12 1 * 1 * 1 1
13 1 * 0 * 4 0
14 1 * 1 * 3 3
15 1 * 1 * 2 2
16 0 * 1 * 8 0
17 1 * 1 * 6 6
18 1 * 1 * 8 8
19 1 * 1 * 7 7
20 1 * 1 * 6 6

Table 5.

Table 6. below shows you the same virtual representation of 1/0 numerical values without the numbers sold column, that is using SUMPRODUCT to count the number of rows satisfying the two conditions, or


9 1 * 0 = 0
10 0 * 0 = 0
11 1 * 1 = 1
12 1 * 1 = 1
13 1 * 0 = 0
14 1 * 1 = 1
15 1 * 1 = 1
16 0 * 1 = 0
17 1 * 1 = 1
18 1 * 1 = 1
19 1 * 1 = 1
20 1 * 1 = 1

Table 6.
If you have been able to follow this explanation all of the way through, it may have occurred to you that although we are using the SUMPRODUCT function, the '*' operators have resolved the multiple arrays into a single composite array, leaving SUMPRODUCT to simply sum the members of that composite array, that is, there is no product. This is perfectly correct, and perfectly valid, SUMPRODUCT can work on a single array (put 1,2,3 in cells A1,A2,A3, and insert =SUMPRODUCT(A1:A3) in a cell, it returns 6 correctly). In reality, we only need the '*' to coerce the arrays that are being tested for a particular condition, we do not need it for the array that is not subject to a conditional test. So we could also use
which does use the product aspect (see more on this in the next section)

When using the SUMPRODUCT function, all arrays must be the same size, as corresponding members of each array are multiplied by each other.

When using the SUMPRODUCT function, no array can be a whole column (A:A), the array must be for a range within a column (although the best part of a column could be defined with A1:A65535 if so desired). Whole rows (1:1) are acceptable.

In a SUMPRODUCT function, the arrays being evaluated cannot be a mix of column and row ranges, they must all be columns, or all rows. However, the row data can be transposed to present it to SUMPRODUCT as columnar - see the Using TRANSPOSE to test against values in a column not row example.


In the examples presented so far, the format has been

As mentioned above, we could also use
which works as the '*' operator is only required to coerce the conditional arrays that resolve to TRUE/FALSE into numeric values.

As it the use of a arithmetic operator that coreces the TRUE/FALSE values to 1/0, we could use many different operators and achieve the same result. Thus, it is also possible to coerce each of the conditional arrays individually by multiplying them by 1,

or by raising to the power of 1,

or by adding 0,

or even by using the N function,

These methods differ from the '*' operator in that they are applied to individual arrays, '*' operates on two arrays.

All of these methods work, when there is more than one conditional array, so it is really a matter of preference as to which to use. If there is a single conditional array, then the '*' operator cannot be used (there are not two to multiply), so one of the other above methods has to be used.

Yet another method is to use the double unary operator, --, in this way
The double unary operator also coerces the indivual array(s), which then acts more akin to classic SUMPRODUCT.

There has been much discussion that one way is faster than another, or is more of a 'standard' than another, but in reality there will be few instances where one method will gain a noticeable performance advantage over another, and as for standards, this is all new territory, and will mainly be used by people who have never been involved in using these standards, and who care even less.

For me, I believe it is a matter of preference. Personally, I am being swayed to the double unary -- notation, because it avoids a function call, it works in all situations (the '*' operator won't work on a single array), and I don't like the '1*', '*1', '^1', or '+0' variations. So my preference is for
which also has more similarity to classic SUMPRODUCT,

There is one other varitaion which has been promoted recently, which is the single unary operator, '-', such as
but I would not encourage this as it has no real merit that I can see, and has to be paired off, otherwise it will return a negative result.

So, to sum up ...
Tests, like A=10 normally resolve to TRUE or FALSE, and any operator is only needed if you want to coerce an array of TRUE/FALSE values to 1/0 integers, such as

SUMPRODUCT arrays are normally separated by the comma. So, to preserve this format, if you have multiple conditions, you can use the -- on both conditions like so

But, if you simply multiply two arrays of TRUE/FALSE, that implicitly resolves to 1/0 values that are then summed, you don;t need comma, so you could then use

Any further, final, array of values can use the same operator, or could revert to comma. So your formula can be written as

If the result is the product of two conditions being multiplied, it is fine to multiply them together as this will coerce the True/False values to 1/0 values to allow the summing

However, if there is only one condition, you can coerce to 1/0 with the double unary --

You could achieve this equally as well with

and equally the first could be represented as

There is no situation that I know of whereby a solution using -- could not be achieved somehow with a '*'. Conversely, if using the TRANSPOSE function within SUMPRODUCT, then the '*' has to be used.

So, as you can see there are a number of possibilities, and you make your own choice. I leave the final word to Harlan Grove, who once wrote this paragraph on why he prefers the double unary operator ... I've written before, it's not the speed of double unary minuses I like, it's the fact that due to Excel's operator precedence it's harder to screw up double unary minuses with typos than it is to screw up the alternatives ^1, *1, +0. Also, since I read left to right, I prefer my number type coercions on the left rather than the right of my Boolean expressions, and -- looks nicer than 1* or 0+. Wrapping Boolean expressions inside N() is another alternative, possibly clearer, but it eats a nested function call level, so I don't use it.


Matching against values in another range
Dates for any international setting
Using TRANSPOSE to test against values in a column not row
Testing against multiple non-contiguous ranges
Find instances of a string, ignoring leading or trailing spaces
Count the number of unique values in a range
Avoid double-counting in multiple conditions
Count items matching a list
Count partial matching in a range
Count beteween two dates, excluding holidays
Sum visible cells



Belajar Memasang Kabel UTP Sendiri

Posted by itokwrote in Computer Networking.

Semua pasti tahu kan kalo yang namanya wired network kalo gak bener kabel ke konektor RJ-45 nya bisa-bisa network gak bakalan jalan. Belum lagi RJ-45 yang cuman sekali pake kalo salah pasang atau kurang pas pemasangan gak bisa dibenerin dan dipake lagi. Sebener nya pemasangan nya mudah banget tapi ternyata gak semua orang tau gimana cara memasang kabel UTP ke RJ-45 dengan benar dan bahkan lebih banyak lagi yang gak tau apa alasan kabel UTP harus dipasang seperti itu.Seperti biasa bakalan rame dengan gambar jadi sabar aja buka nya ya :D.

Alat dan bahan yang diperlukan :

Kabel UTP

Kabel UTP sebetulnya ada beberapa kategori yaitu dari kategori 1 - 7 yang sering digunakan untuk LAN biasanya kategori 5 atau sering disebut cat-5. Berikut ini kegunaan dari kabel kategori 1 - 7 diambil dari wikipedia.

cat 1: sebelumnya dipakai untuk POST (Plain Old Telephone Service) telephone dan ISDN.

cat 2: dipakai untuk token ring network dengan bw 4mbps

cat 3: dipakai untuk data network dengan frequensi up to 16Mhz dan lebih populer untuk pemakaian 10mbps

cat 4: Frequensi up to 20Mhz dan sering dipakai untuk 16mbps token ring network.

cat 5: Frequensi up to 100Mhz dan biasa dipakai untuk network dengan kecepatan 100Mbps tetap kemungkinan tidak cocok untuk gigabyte ethernet network.

cat 5e: Frequensi dan kecepatan sama dengan cat-5 tetapi lebih support gigabyte ethernet network.

cat 6: Memiliki kecepatan up to 250Mbps atau lebih dari dua kali cat-5 dan cat-5e

cat 6a: Kabel masa depan untuk kecepatan up to 10Gbps

cat 7: di design untuk bekerja pada frequensi up to 600Mhz.

Berikut ini contoh gambar kabel UTP yang sudah dipasang konektor, kabel cat-5e dalam keadaan terkupas dan kabel cat-6.


RJ-45 Connector

Setelah anda tahu jenis-jenis kabel sekarang konektor RJ-45 biar gak pusing ini gambarnya dan perbedaan nya dengan RJ-11 yang juga sering ditemukan dipasaran. Gambar yang atas adalah RJ-45 dengan 8 pin sedangkan yang bawah adalah RJ-11 dengan hanya memiliki 4 pin.


Crimp Tool

Crimp tool / Crimping tool adalah alat untuk memasang kabel UTP ke konektor RJ-45 / RJ-11 tergantung kebutuhan. Bentuknya macam-macam ada yang besar dengan fungsi yang banyak, seperti bisa memotong kabel, mengupas dan lain sebagainya. Ada juga yang hanya diperuntukan untuk crimp RJ-45 atau RJ-11 saja. Contoh gambarnya seperti ini.


Kabel Tester

Supaya anda yakin bahwa pemasangan kabel ke konektor sudah ok lebih baik kalau anda juga memiliki cable tester seperti berikut ini. Perbedaan diatara dua testerdibawah ini adalah yang satu memakai satu led untuk satu pair sementara yang satu lagi satu led untuk satu kabel. Untuk pemula lebih mudah untuk mempergunakan yang type satu led per kabel karena anda tidak akan dibuat pusing :D. Kemudian tester yang lebih kecil adalah remote cable tester yang dipakai apabila kabel yang di test panjang dan kedua ujung nya tidak berdekatan (misalnya ada diruangan yang berbeda). Cara penggunaannya adalah dengan memasang ujung kabel yang satu ke TX di cable tester yang besar kemudian set auto, kemudian di ujung yang lain kita pasang remote cable tester. Setelah itu anda cukup melihat remote cable tester saja. Apabila menyala berarti kabel terkoneksi dengan baik sementara apabila mati berarti kabel terputus.

Sedikit catatan: hasil test dengan menggunakan kabel tester tidak berarti menunjukan bahwa kabel tersebut bisa berfungsi dengan baik. Jarak maksimum 100meter dari kabel cat-5e kadang apabila di test dengan cable tester akan tetap menghasilkan nilai baik pada jarak lebih dari 100meter sementara ketika dialiri data koneksi terputus karena kabel terlalu panjang.

Alat yang berikutnya adalah tone generator yang mampu melakukan tracing di posisi mana kabel putus. Sangat berguna apabila anda tidak menginginkan untuk mengganti seluruh kabel ketika ada kerusakan.


Setelah anda tahu alat-alat yang diperlukan untuk pemasangan kabel UTP ke RJ-45 soket, sekarang ada istilah dalam stright dan crossover dalam cabling.

Dari 8 kabel (4 pair) UTP kabel, yang terpakai sebetulnya hanya 4 kabel (dua pair). dua kabel untuk TX atau transfer data dan dua kabel untuk RX atau menerima data. Walaupun hanya empat kabel yang terpakai, kita tidak boleh sembarangan mengambil kabel mana saja yang akan dipakai. Kabel yang dipakai haruslah dua pair atau dua pasang. Tanda kabel satu pasang adalah kabel tersebut saling melilit dan memiliki warna / stripe yang sama. Menurut standar TIA/EIA-568-B pasangan kabel yang dipakai adalah pasangan orange-orange putih dan hijau-hijau putih.

Sementara pin yang dipakai dari delapan pin yang dimiliki RJ-45 yang terpakai adalah Pin nomor 1-2-3-6 sementara nomor 4-5-7-8 tidak terpakai untuk transfer dan receive data Alias nganggur.

Berikut ini susunan kabel standar menurut warna pada posisi stright dan pada posisi cross.


Cross cable biasa dipakai untuk koneksi dari PC to PC / PC to Router, Pokoknya semua koneksi dari alat yang biasanya koneksi melalui switch atau hub tetapi dipasang secara langsung. Berikut ini contoh posisi kabel dalam kondisi crossover.

Gambar paling kiri adalah posisi warna pada satu sisi dan sisi yang lainnya berdasarkan standar internasional T568A dan T568B. Nomor konektor dihitung dari sebelah kiri dengan kondisi konektor bagian pinnya menghadap kita.

Gambar tengah adalah contoh kabel cross yang sudah jadi dan gambar berikutnya adalah contoh cross over adapter yaitu alat yang bisa membuat stright cable menjadi cross apabila anda tidak ingin merubah konektor dengan cara memotong nya


Sementara untuk stright cable anda tidak perlu repot memikirkan cross over anda cukup menyamakan posisi kabel di satu sisi dengan sisi lainnya.

Ok sekarang anda sudah tahu apa itu cross dan apa itu stright. Sekarang Tips untuk memasang Kabel ke Konektor.

1. Siapkan semua peralatan terutama kabel, konektor RJ-45 dan Crimping tool.

2. Kupas bagian luar kabel (pembungkus kabel-kabel kecil) kira-kira sepanjang 1 cm dengan menggunakan pengupas kabel yang biasanya ada pada crimp tool (bagian seperti dua buah silet saling berhadapan itu untuk mengupas)

3. Susun kabel sesuai dengan keperluan. Untuk konektor pertama selalu susun dengan susunan standar untuk Stright atau T568A. Apabila anda merasa kurang nyaman dengan susunan kabel coba tarik sedikit semua kabel yang telah dikupas sementara tangan yang satu lagi memegang bagian kabel yang tidak terkupas. Kemudian susun kembali dengan cara memelintir dan membuka lilitan pasangan kabel.

4. Rapihkan susunan kabel dengan cara menekan bagian yang dekat dengan pembungkus kabel supaya susunan kabel terlihat rata.

5. Potong ujung-ujung kabel yang tidak rata dengan pemotong kabel (bagian yang hanya memiliki satu buah pisau dan satu bagian lagi datar pada crimp tool adalah pemotong kabel) sampai rapih. Usahakan jarak antara pembungkus kabel sampai ujung kabel tidak lebih dari 1cm.

6. Dengan tetap menekan perbatasan antara kabel yang terbungkus dan kabel yang tidak terbungkus, coba masukan kabel ke konektor RJ-45 sampai ujung-ujung kabel terlihat dibagian depan konektor RJ-45. Kalau masih belum coba terus ditekan sambil dipastikan posisi kabel tidak berubah.

7. Setelah anda yakin posisi kabel tidak berubah dan kabel sudah masuk dengan baik ke konektor RJ-45 selanjutnya masukan konektor RJ-45 tersebut ke crimpt tool untuk di pres. Ketika konektor dalam kondisi didalam crimp tool anda bisa memastikan kembali kabel sudah sepenuhnya menyentuh bagian dapet RJ-45 dengan cara mendorong kabel kedalam RJ-45. Pastikan juga bahwa bagian pembungkus kabel sebagian masuk kedalam konektor RJ-45.

8. Kemudian anda bisa menekan crimp tool sekuat tenaga supaya semua pin RJ-45 masuk dan menembus pelindung kabel UTP yang kecil. Apabila anda kurang kuat menekan kemungkinan kabel UTP tidak tersobek oleh pin RJ-45 sehingga kabel tersebut tidak konek. Dan apabila pembungkus bagian luar tidak masuk kedalam konektor RJ-45, apabila kabel tersebut sering digerak-gerakan, kemungkinan besar posisi kabel akan bergesar dan bahkan copot.

9. Lakukan langkah-langkah diatas untuk ujung kabel yang satu nya lagi.

10. Apabila anda yakin sudah memasang kabel UTP ke RJ-45 dengan kuat selanjutnya adalah test dengan menggunakan LAN tester apabila ada. Apabila anda tidak memiliki LAN tester jangan takut anda cukup melihat kembali kabel yang sudah terpasang, memastikan bahwa anda sudah cukup kuat memasang nya dan semua ujung kabel terlihat dari bagian depan RJ-45 maka hampir bisa dipastikan pemasangan kabel UTP tersebut sukses.

11. Silahkan di coba dan good luck

yang punya:



Power Rangers

Tempo: 180 BPM

8d2, 8-, 8d2, 8-, 8c2, 8d2, 8-, 8f2, 8-, 8d2, 4-, 8d2, 4-, 4a2, 4-, 8g2, 4-, 8f2, 4-, 8e2, 8-, 8d2, 8-, 8d2, 8-, 8c2, 8d2, 8-, 8f2, 8-, 8d2


Last Post

[Most Recent Quotes from] [Most Recent Quotes from]

Leave a Comment

Free shoutbox @ ShoutMix

Link Exchange