공백일 경우 합계 – Excel 및 Google 스프레드시트

이 튜토리에서는 SUMIFS함수를 사용하여 Excel 및 Google 스프레드시트에서 빈 셀 또는 비어 있는 셀에 해당하는 데이터를 합산하는 방법을 보여드립니다.

빈칸인 경우 합계

먼저 빈 셀이 있는 행을 합산하는 방법을 보여드리겠습니다.

SUMIFS 함수는 특정 기준을 충족하는 데이터를 합산합니다.

아래 예제에서 이름이 없는 선수의 모든 점수를 합산하는 데 SUMIFS 함수를 사용할 수 있습니다.

=SUMIFS(C3:C8,B3:B8,"")

Excel에서는 큰따옴표(“”)를 사용하여 빈 셀을 나타냅니다. 이 예에서는 선수 A, B, C, D는 무시하고 알 수 없는 선수의 점수를 합산합니다.

공백을 빈 셀로 처리하기 – 도우미 열 사용

Excel에서 빈 셀을 취급할 때는 주의해야 합니다. 셀이 공백으로 보일 수 있지만 Excel에서는 공백으로 취급하지 않습니다. 셀에 공백, 줄 바꿈 또는 기타 보이지 않는 문자가 포함된 경우 이런 문제가 발생할 수 있습니다. 이는 다른 소스에서 Excel로 데이터를 가져올 때 흔히 발생하는 문제입니다.

공백만 포함된 셀을 빈칸인 경우와 같은 방식으로 처리해야 하는 경우 이전 예제의 수식이 작동하지 않습니다. SUMIFS 수식이 아래 B9 셀(” “)을 빈셀로 간주하지 않는 것을 주목하세요.

공백만 포함된 셀을 빈 셀인 것처럼 처리하려면 TRIM 함수를 사용해 도우미 열을 추가하여 각 셀의 값에서 여분의 공백을 제거할 수 있습니다.

=TRIM(B3)

도우미 열에 SUMIFS 함수를 적용하면 이제 합계를 정확하게 계산합니다.

=SUMIFS(E3:E9,D3:D9,"")

도우미 열은 만들기도 쉽고 읽기 쉽지만 작업을 수행하기 위해 단일 수식을 사용하고 싶을 수도 있습니다. 이에 대해서는 다음 섹션에서 다룹니다.

도우미 열 없이 공백을 빈 셀로 처리하기

도우미 열이 적합하지 않은 경우 SUMPRODUCT 함수를 LEN 및 TRIM 함수와 함께 사용하여 빈 행을 합산할 수 있습니다.

=SUMPRODUCT(--(LEN(TRIM(B3:B9))=0),D3:D9)

이 예제에서는 SUMPRODUCT 함수를 사용하여 복잡한 “sum if” 계산을 수행합니다. 수식을 살펴봅시다.

이것이 최종 수식입니다.

=SUMPRODUCT(--(LEN(TRIM(B3:B9))=0),D3:D9)

먼저, SUMPRODUCT 함수는 두 셀 범위의 값 배열을 나열합니다:

=SUMPRODUCT(--(LEN(TRIM({"A"; "B"; ""; "C"; ""; "XX"; " "}))=0),{25; 10; 15; 5; 8; 17; 50})

그런 다음 TRIM 함수는 선수 이름에서 앞뒤 공백을 제거합니다:

=SUMPRODUCT(--(LEN({"A"; "B"; ""; "C"; ""; "XX"; ""})=0),{25; 10; 15; 5; 8; 17; 50})

LEN 함수는 공백이 제거된 선수 이름의 길이를 계산합니다:

=SUMPRODUCT(--({1; 1; 0; 1; 0; 2; 0}=0),{25; 10; 15; 5; 8; 17; 50})

논리 테스트(=0)를 사용하면 공백이 제거된 선수 이름 리스트에서  이름이 0글자인 값이 TRUE로 변경됩니다:

=SUMPRODUCT(--({FALSE; FALSE; true; false; false; true; false; true}),{25; 10; 15; 5; 8; 17; 50})

다음으로 이중 대시(–)는 TRUE 및 FALSE 값을 1과 0으로 변환합니다:

=SUMPRODUCT({0; 0; 1; 0; 1; 0; 1},{25; 10; 15; 5; 8; 17; 50})

그런 다음 SUMPRODUCT 함수는 배열의 각 항목 쌍을 곱하여 비어 있거나 공백으로만 이루어진 선수 이름에 대해서만 점수 배열을 생성합니다.

=SUMPRODUCT({0; 0; 15; 0; 8; 0; 50})

마지막으로 배열의 숫자를 모두 합산합니다.

=73

합계 함수에서 부울 문과 “–” 명령을 사용하는 방법에 대한 자세한 내용은 여기에서 확인할 수 있습니다.

Google 스프레드시트에서 공백인 경우 합계

이 수식은 Google 스프레드시트에서도 Excel과 똑같이 작동합니다.