Mengambil Data dengan Multi Kriteria

Langsung saja ke TKP

Tabel Kasus:

4 Kriteria :

Dan kita ingin mengambil data pada kolom ke 5 (Kolom Sales).

Rumus 1:
=SUMIFS(E2:E11;A2:A11;B14;B2:B11;B15;C2:C11;C14;D2:D11;C15)

Rumus 2:
=SUMPRODUCT(E2:E11;(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 3: Rumus Array
=INDEX(E2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0))

Rumus 4: Rumus Array
=VLOOKUP(B14;IF((B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);A2:E11;"");5;FALSE)

Rumus 5: Rumus Array
=SUM(E2:E11*(B2:B11=B15)*(A2:A11=B14)*(C2:C11=C14)*(D2:D11=C15))

Rumus 6: Rumus Array
=VLOOKUP(B14&"/"&B15&"/"&C14&"/"&C15;CHOOSE({1\2\3\4};A2:A11&"/"&B2:B11&"/"&C2:C11&"/"&D2:D11;E2:E11);2;FALSE)

Rumus 7: Rumus Array
=INDEX(E2:E11;MATCH(1;(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15);0))

Rumus 8: Rumus Array
=INDEX(E2:E11;SUMPRODUCT(--(A2:A11=B14)*(B2:B11=B15)*(C2:C11=C14)*(D2:D11=C15)))

Rumus 9: Rumus Array
=INDEX(E2:E11;MATCH(1;IF(A2:A11=B14;IF(B2:B11=B15;IF(C2:C11=C14;IF(D2:D11=C15;1));0))))

Rumus 10: Rumus Array
=IFERROR(INDEX(E:E;MATCH(1;(A:A=B14)*(B:B=B15)*(C:C=C14)*(D:D=C15);0));0)

Rumus 11: Rumus Array
=INDEX(A2:E11;MATCH(B14&B15&C14&C15;A2:A11&B2:B11&C2:C11&D2:D11;0);5)

dan mungkin masih ada variasi rumus yang lain dan belum ditemukan...... silahkan anda coba2x

Download File Latihan