여러 시트에 걸쳐 SUMIF 함수 사용하기

Written by

Editorial Team

Reviewed by

Steve Rynearson

Translated by

Younjung Kim

Last updated on 5월 19, 2023

이 자습서에서는 SUMPRODUCT 및 SUMIFS 함수를 사용하여 Excel 및 Google 스프레드시트의 여러 시트에서 특정 기준을 충족하는 데이터를 합산하는 방법을 보여 줍니다.

여러 시트에 걸쳐 일반적인 SUM 함수 사용하기

데이터가 여러 워크시트에 나누어져 있는 경우가 있습니다. 주기적으로 수집되는 데이터의 경우 여러 워크시트에 데이터가 나누어져 있을 수 있습니다 . 통합 문서의 각 시트에는 설정된 기간 동안의 데이터가 포함될 수 있습니다. 두 개 이상의 시트에 포함된 데이터를 합산하는 수식이 필요한 경우 아래 내용을 참조하세요.

SUM 함수에 3D 참조를 사용하여 여러 시트의 데이터를 쉽게 합산할 수 있습니다:

=SUM(Sheet1:Sheet2!A1)

SUMIFS 함수에서는 이 기능을 사용할 수 없고 더 복잡한 수식을 사용해야 합니다.

여러 시트에 걸쳐 SumIf 함수 사용하기

아래 예제에서는 각각 다른 월의 데이터가 있는 여러 워크시트에 걸쳐 각 고객의 배송 예정 건수를 SUMIFS, SUMPRODUCT 및 INDIRECT 함수를 사용하여 합산합니다:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3))

수식을 좀 더 자세히 살펴보겠습니다.

1단계: 시트 하나에 대해서만 SUMIFS 수식 만들기:

단일 입력 데이터 시트에 대한 고객별 배송 예정 건수를 합산하기 위해 SUMIFS 함수를 사용합니다:

=SUMIFS(D3:D7,C3:C7,H3)

2단계: 수식에 시트 참조 추가하기

수식 결과는 동일하게 유지하되 입력 데이터가 ‘Step 2‘라는 시트에 있을 경우 아래의 수식을 참조해 주세요.

=SUMIFS('2단계'!D3:D7,'2단계'!C3:C7,H3)

3단계: SUMPRODUCT 함수 내에 중첩해서 사용하기

여러 시트에 걸쳐 SUMIFS 계산을 수행하고 결과를 합산하는 수식을 만드려면 기존 SUMIFS 함수에 SUMPRODUCT 함수를 추가해야 합니다.

=SUMPRODUCT(SUMIFS('3단계'!D3:D7,'3단계'!C3:C7,H3))

한 시트에서 SUMIFS 함수를 사용하면 단일 값이 산출됩니다. 여러 시트에 걸쳐 SUMIFS 함수는 각 워크시트에 대해 하나씩 값 배열을 출력합니다. 이 배열의 값을 합산하기 위해 SUMPRODUCT 함수를 사용합니다.

4단계: 시트 참조를 시트 이름 목록으로 바꾸기

수식의 시트 이름 부분을 데이터 목록으로 바꾸려고 합니다: 이 목록은 F3:F6 셀에 있습니다(1월, 2월, 3월, 4월).

시트 이름을 표시하는 텍스트 목록이 SUMIFS 함수에서 유효한 셀 참조의 일부로 취급되도록 하려면 INDIRECT 함수를 사용해야 합니다.

=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3))

이 수식에서는 이전에 작성된 범위 참조 기능이 사용됩니다:

'3단계'!D3:D7

아래와 같이 실제 수식에 위 수식의 기능을 응용하였습니다:

INDIRECT("'"&F3:F6&"'!"&"D3:D7")

따옴표로 인해 수식을 읽기 어려우므로 아래와 같이 공백을 추가하여 표시하였습니다:

INDIRECT ( " ' " & F3:F6 & " ' ! " & "D3:D7" )

셀 목록을 참조하는 위의 방법을 사용하면 숫자 목록 스타일을 따르지 않는 여러 시트의 데이터를 요약할 수도 있습니다. 표준 3D 참조를 사용하려면 시트 이름이 Input1, Input2, Input3 식의 스타일이어야 하지만, 위의 예제에서는 시트 이름을 별도의 셀에서 참조하여 사용하였기 때문에 표준 스타일과 관계없이 어떤 시트 이름도 사용할 수 있었습니다.

셀 참조 잠그기

위 예제에서는 수식을 더 쉽게 읽을 수 있도록 셀 참조를 잠그지 않은 수식을 표시했습니다:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&F3:F6&"'!"&"D3:D7"),INDIRECT("'"&F3:F6&"'!"&"C3:C7"),H3))

하지만 이러한 수식은 파일의 다른 곳에 복사하여 붙여넣으면 제대로 작동하지 않을 수 있습니다. 다른 곳에 복사해서 사용하려면 다음과 같이 잠긴 셀 참조를 사용해야 합니다:

=SUMPRODUCT(SUMIFS(INDIRECT("'"&$F$3:$F$6&"'!"&"D3:D7"),INDIRECT("'"&$F$3:$F$6&"'!"&"C3:C7"),H3))

자세한 내용은 셀 참조 잠그기 관련 튜토리얼을 읽어보세요.

Google 스프레드시트에서 여러 시트에 걸쳐 SumIf 함수 사용하기

현재 Google 스프레드시트에서는 INDIRECT 함수를 사용하여 SUMPRODUCT 및 SUMIFS 함수에서 시트 목록을 참조할 수 없습니다.

대신 각 입력 시트에 대해 별도의 SUMIFS 계산을 수행하고 그 결과를 합산할 수 있습니다:Instead, separate SUMIFS calculations can be made for each input sheet and the results added together:

=SUMIFS('1월'!C3:C7,'1월'!B3:B7,E3) 
+SUMIFS('2월'!C3:C7,'2월'!B3:B7,E3) 
+SUMIFS('3월'!C3:C7,'3월'!B3:B7,E3) 
+SUMIFS('4월'!C3:C7,'4월'!B3:B7,E3)