다른 열에 특정 텍스트가 포함된 경우 합계 구하기

이 튜토리얼에서는 다른 열에 특정 또는 부분 텍스트가 포함된 경우 값을 합산하는 방법을 보여드리겠습니다. 아래 데이터 범위를 예로 들어, '티셔츠(T-Shirt)'라는 텍스트가 포함된 제품의 총 금액을 구하려면 SUMIF 함수와 SUMPRODUCT 함수 모두 이 작업을 처리할 수 있습니다.

• SUMIF 함수를 사용하여 특정 또는 부분 텍스트가 포함된 경우 값 합산하기
• SUMPRODUCT 함수를 사용하여 특정 또는 부분 텍스트가 포함된 경우 값 합산하기

SUMIF 함수를 사용하여 특정 또는 부분 텍스트가 포함된 경우 값 합산하기

다른 열에 특정 텍스트가 포함된 경우 값을 합산하려면 와일드카드(*)를 사용한 SUMIF 함수를 사용할 수 있습니다. 일반적인 구문은 다음과 같습니다:

하드코딩된 텍스트를 사용한 일반 공식:

• 범위: 기준에 따라 평가하려는 데이터 범위입니다;
• *텍스트*: 값을 합산하기 위한 기준입니다. 여기서 * 와일드카드는 임의의 문자열을 찾기 위해 사용되며, 특정 텍스트를 포함하는 모든 항목과 일치시키기 위해 두 개의 * 사이에 텍스트를 넣습니다. (참고: 텍스트와 와일드카드는 반드시 큰따옴표로 묶어야 합니다.)
• 합계_범위: 합산하려는 숫자 값이 포함된 셀 범위입니다.

셀 참조를 사용한 일반 공식:

• 범위: 기준에 따라 평가하려는 데이터 범위입니다;
• "*"&셀&"*": 값을 합산하기 위한 기준입니다;
  • *: 임의의 문자열을 찾기 위한 와일드카드입니다.
  • 셀: 검색할 특정 텍스트가 포함된 셀입니다.
  • &: 이 연결 연산자(&)는 셀 참조를 별표(*)와 결합하는 데 사용됩니다.
• 합계_범위: 합산하려는 숫자 값이 포함된 셀 범위입니다.

함수의 기본 작동 원리를 이해한 후에는 필요에 따라 다음 공식 중 하나를 사용하고 Enter 키를 눌러 결과를 얻으세요:

참고: 이 SUMIF 함수는 대소문자를 구분하지 않습니다.

SUMPRODUCT 함수를 사용하여 특정 또는 부분 텍스트가 포함된 경우 값 합산하기

SUMPRODUCT 함수도 와일드카드를 사용하지 않고 이 작업을 수행하는 데 도움이 될 수 있습니다. 여기서는 ISNUMBER 및 SEARCH 함수를 SUMPRODUCT 함수 내부에 감싸야 하며, 일반적인 구문은 다음과 같습니다:

• 합계_범위: 합산하려는 숫자 값이 포함된 셀 범위입니다;
• 기준: 값을 합산하기 위한 기준입니다. 셀 참조 또는 정의된 특정 텍스트일 수 있습니다;
• 범위: 기준에 따라 평가하려는 데이터 범위입니다;

아래 공식 중 하나를 빈 셀에 입력하고 Enter 키를 눌러 결과를 반환하세요:

이 공식에 대한 설명:

=SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))))

• SEARCH("T-Shirt",$A$2:$A$12): SEARCH 함수는 데이터 범위 A2:A12에서 특정 텍스트 “T-Shirt”의 위치를 반환합니다. 따라서 다음과 같은 배열을 얻게 됩니다: {5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}.
• ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12))= ISNUMBER({5;#VALUE!;#VALUE!;7;#VALUE!;7;#VALUE!;#VALUE!;#VALUE!;#VALUE!;7}): 이 ISNUMBER 함수는 숫자 값을 테스트하는 데 사용되며, 새로운 배열을 반환합니다: {TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}.
• $B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))= {347;428;398;430;228;379;412;461;316;420;449}*{TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;TRUE}: 두 배열을 곱하면 수학적 연산이 TRUE 및 FALSE 값을 1과 0으로 변환합니다. 따라서 두 배열의 곱셈 결과는 다음과 같습니다: {347;428;398;430;228;379;412;461;316;420;449}*{1;0;0;1;0;1;0;0;0;0;1}={347;0;0;430;0;379;0;0;0;0;449}.
• SUMPRODUCT($B$2:$B$12*(ISNUMBER(SEARCH("T-Shirt",$A$2:$A$12)))) =SUMPRODUCT({347;0;0;430;0;379;0;0;0;0;449}): 마지막으로, SUMPRODUCT 함수는 배열의 모든 값을 합산하여 결과를 얻습니다: 1605.

관련 함수:

추가 문서: