COUNTIF IF DAN MAX - RUMUS EXCEL

COUNTIF

                 Fungsi COUNTIF Dalam Rumus Excel dapat digunakan untuk menghitung banyaknya sel yang memenuhi syarat, kriteria atau kondisi tertentu. Misalnya menghitung banyaknya sel kosong, menghitung sel berisi bilangan tertentu, sel berisi tanggal tertentu, maupun sel berisi text atau karakter tertentu.

=COUNTIF($B$2:$B$9;C2)
=COUNTIF($B$2:$B$9;'"p")

=COUNTIF(C2:C8;">"&E4)

=COUNTIF(C2:C8;80)

=COUNTIF(B2:B8;"Apel")

=Countif($B$2:$B$9;Max($B$2:$B$9))

=IF(COUNTIF(B2:B3;B3)=2;0;IF(COUNTIF(B3:B3;B3)=1;B3;0))

Membuat No Urut Mulai di Cell Aktif - Macro Pemula

Sub test1()

‘Penabahan 1 berulang sampai angka 10

No = 0

For NOMOR = 1 To 10

No = No + 1

‘Mulai Baris ke 5 di kolom 1

Cells(No + 5, 1).Value = No

Next NOMOR

End Sub

Sub test2()

'No_urut_mulai_aktifCell

No = 0

y = ActiveCell.Row - 1

Z = (ActiveCell.Column)

yy = y - y + 1

For x = yy To 10

No = No + 1

Cells(No + y, Z).Value = No

Next x

End Sub

Sub test3()

'No_urut_mulai_aktifCell_1_kolom_kesamping

No = 0

y = ActiveCell.Row - 1

Z = (ActiveCell.Column) + 1

yy = y - y + 1

For x = yy To 10

No = No + 1

Cells(No + y, Z).Value = No

Next x

End Sub

Sub test4()

‘Penabahan 1 berulang sesuai No Baris

For i = 1 To10

Cells(i, 1) = i

Cells(i, 2) = i + 1

Cells(i, 3) = i + 2

Cells(i, 4) = i + 3

Next i

End Sub

MEMBUAT NO URUT OTOMATIS DI EXCEL - VBA SCRIP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Sheets("sheet1").Range("a1") = Application.CountA(Range("B2:B20"))

No = 0

For NOMOR = 1 To Range("a1")

No = No + 1

Cells(No + 1, 1).Value = No

Next NOMOR

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

[a2:a30].Formula = "=IF(B2<>0,COUNTA($B$2:B2),0)"

End Sub

RUMUS :

=IF(B2<>0;COUNTA($B$2:B2);0)

=IF(B2<>0;COUNTIF($B$2:B2;">0");0)