이 튜토리얼은 Google 스프레드시트에서 SUMIF 및 SUMIFS 함수를 사용하는 방법에 대한 자세한 데모를 수식 및 예제와 함께 제공합니다.
SUMIF는 조건부로 셀 합계에 사용되는 Google 스프레드시트의 수학 함수 중 하나입니다. 기본적으로 SUMIF 함수는 셀 범위에서 특정 조건을 찾은 다음 주어진 조건에 맞는 값을 더합니다.
예를 들어 Google 시트에 비용 목록이 있고 특정 최대값을 초과하는 비용만 합산하려고 합니다. 또는 주문 항목 및 해당 금액 목록이 있고 특정 항목의 총 주문 금액만 알고 싶습니다. SUMIF 함수가 유용한 곳입니다.
SUMIF를 사용하여 숫자 조건, 텍스트 조건, 날짜 조건, 와일드카드는 물론 비어 있거나 비어 있지 않은 셀을 기반으로 값을 합산할 수 있습니다. Google 스프레드시트에는 SUMIF 및 SUMIFS라는 기준에 따라 값을 요약하는 두 가지 기능이 있습니다. SUMIF 함수는 하나의 조건을 기준으로 숫자를 더하는 반면 SUMIFS는 여러 조건을 기반으로 숫자를 합산합니다.
이 튜토리얼에서는 Google 스프레드시트의 SUMIF 및 SUMIFS 함수를 사용하여 특정 조건을 충족하는 숫자의 합계를 구하는 방법을 설명합니다.
Google 스프레드시트의 SUMIF 함수 – 구문 및 인수
SUMIF 함수는 SUM과 IF 함수의 조합일 뿐입니다. IF 함수는 주어진 조건에 대해 셀 범위를 검색한 다음 SUM 함수는 조건을 충족하는 셀에 해당하는 숫자를 합산합니다.
SUMIF 함수의 구문:
Google 스프레드시트의 SUMIF 함수 구문은 다음과 같습니다.
=SUMIF(범위, 기준, [합계 범위])인수:
범위 - 기준을 충족하는 셀을 찾는 셀 범위입니다.
기준 – 추가해야 하는 셀을 결정하는 기준입니다. 숫자, 텍스트 문자열, 날짜, 셀 참조, 표현식, 논리 연산자, 와일드카드 문자 및 기타 기능을 기준으로 기준을 설정할 수 있습니다.
합계 범위 – 이 인수는 선택 사항입니다. 해당 범위 항목이 조건과 일치하는 경우 합산할 값이 있는 데이터 범위입니다. 이 인수를 포함하지 않으면 '범위'가 대신 합산됩니다.
이제 SUMIF 함수를 사용하여 다른 기준으로 값을 합하는 방법을 살펴보겠습니다.
숫자 기준이 있는 SUMIF 함수
다음 비교 연산자 중 하나를 사용하여 기준을 만들어 셀 범위에서 특정 기준을 충족하는 숫자를 합산할 수 있습니다.
- 보다 큼(>)
- 미만(<)
- 크거나 같음(>=)
- 이하(<=)
- 같음(=)
- ()와 같지 않다
다음 스프레드시트가 있고 1000개 이상의 총 판매액에 관심이 있다고 가정합니다.
SUMIF 함수를 입력하는 방법은 다음과 같습니다.
먼저 합계 출력을 표시할 셀(D3)을 선택합니다. 1000보다 크거나 같은 B2:B12의 숫자를 합산하려면 이 수식을 입력하고 'Enter'를 누르십시오.
=SUMIF(B2:B12,">=1000",B2:B12)이 예제 수식에서는 판매 번호와 기준이 동일한 범위에 적용되므로 range 및 sum_range 인수(B2:B12)가 동일합니다. 그리고 기준은 셀 참조를 제외하고 항상 큰따옴표로 묶어야 하기 때문에 비교 연산자 앞에 숫자를 입력하고 따옴표로 묶었습니다.
수식은 1000보다 크거나 같은 숫자를 찾은 다음 일치하는 모든 값을 더하고 결과를 셀 D3에 표시했습니다.
range 및 sum_range 인수가 동일하기 때문에 다음과 같이 수식에 sum_range 인수 없이도 동일한 결과를 얻을 수 있습니다.
=SUMIF(B2:B12,">=1000")
또는 숫자 기준 대신 숫자가 포함된 셀 참조(D2)를 제공하고 기준 인수에서 비교 연산자를 해당 셀 참조와 결합할 수 있습니다.
=SUMIF(B2:B12,">="&D2)보시다시피 비교 연산자는 여전히 큰따옴표로 묶여 있으며 연산자와 셀 참조는 앰퍼샌드(&)로 연결됩니다. 그리고 셀 참조를 따옴표로 묶을 필요가 없습니다.
메모: 기준이 포함된 셀을 참조할 때 셀의 값에 선행 또는 후행 공백을 두지 않도록 하십시오. 참조된 셀의 값 앞뒤에 불필요한 공백이 있는 경우 수식은 결과적으로 '0'을 반환합니다.
다른 논리 연산자를 동일한 방식으로 사용하여 기준 인수에서 조건을 만들 수도 있습니다. 예를 들어 500보다 작은 값을 합산하려면 다음을 수행합니다.
=SUMIF(B2:B12,"<500")
숫자가 다음과 같은 경우 합계
특정 숫자와 같은 숫자를 추가하려면 숫자만 입력하거나 기준 인수에 등호가 있는 숫자를 입력하면 됩니다.
예를 들어 값이 20인 수량(C열)에 대한 해당 판매 금액(B열)을 합산하려면 다음 공식 중 하나를 시도하십시오.
=SUMIF(C2:C12,"=20",B2:B12)
=수미프(C2:C12,"20",B2:B12)
=수미프(C2:C12,E2,B2:B12)
C 열의 20과 같지 않은 수량으로 B 열의 숫자를 합산하려면 다음 공식을 시도하십시오.
=수미프(C2:C12,"20",B2:B12)
텍스트 기준이 있는 SUMIF 함수
특정 텍스트가 있는 셀에 해당하는 셀 범위(열 또는 행)의 숫자를 더하려면 SUMIF 수식의 기준 인수에 해당 텍스트 또는 텍스트가 포함된 셀을 포함하기만 하면 됩니다. 텍스트 문자열은 항상 큰따옴표(" ")로 묶어야 합니다.
예를 들어 '서부' 지역의 총 판매액을 원하면 아래 공식을 사용할 수 있습니다.
=SUMIF(C2:C13,"서쪽",B2:B13)이 수식에서 SUMIF 함수는 C2:C13 셀 범위에서 'West' 값을 검색하고 B 열에서 해당 판매 값을 더합니다. 그런 다음 결과를 E3 셀에 표시합니다.
기준 인수에 텍스트를 사용하는 대신 텍스트가 포함된 셀을 참조할 수도 있습니다.
=수미프(C2:C12,E2,B2:B12)
이제 '서부'를 제외한 모든 지역의 총 수익을 구해 보겠습니다. 그렇게 하려면 공식에서 같지 않음 연산자()를 사용합니다.
=SUMIF(C2:C12,""&E2,B2:B12)
와일드카드가 있는 SUMIF
위의 방법에서 텍스트 기준이 있는 SUMIF 함수는 정확한 지정된 텍스트에 대해 범위를 확인합니다. 그런 다음 정확한 텍스트에 평행한 숫자를 합산하고 부분적으로 일치하는 텍스트 문자열을 포함한 다른 모든 숫자는 무시합니다. 부분적으로 일치하는 텍스트 문자열로 숫자를 합산하려면 기준에서 다음 와일드카드 문자 중 하나를 조정해야 합니다.
?(물음표)는 텍스트 문자열의 임의의 단일 문자와 일치하는 데 사용됩니다.*(별표)는 일련의 문자와 함께 일치하는 단어를 찾는 데 사용됩니다.~(틸드)는 물음표(?) 또는 별표 문자(*)가 있는 텍스트를 일치시키는 데 사용됩니다.
와일드카드를 사용하여 숫자를 합산하기 위해 제품 및 수량에 대한 이 예제 스프레드시트를 사용합니다.
별표(*) 와일드카드
예를 들어, 모든 Apple 제품의 수량을 합산하려면 다음 공식을 사용하십시오.
=SUMIF(A2:A14,"애플*",B2:B14)이 SUMIF 수식은 시작 부분에 "Apple"이라는 단어가 있고 그 뒤에 임의의 수의 문자가 있는 모든 제품을 찾습니다('*'로 표시). 일치하는 항목을 찾으면 다음을 요약합니다. 수량 일치하는 텍스트 문자열에 해당하는 숫자.
기준에 여러 와일드카드를 사용할 수도 있습니다. 또한 직접 텍스트 대신 셀 참조와 함께 와일드카드 문자를 입력할 수도 있습니다.
그렇게 하려면 와일드카드를 큰따옴표(" ")로 묶고 셀 참조와 연결해야 합니다.
=SUMIF(A2:A14,"*"&D2&"*",B2:B14)이 공식은 단어가 문자열의 어디에 위치하든 상관없이 'Redmi'라는 단어가 포함된 모든 제품의 수량을 더합니다.
물음표(?) 와일드카드
물음표(?) 와일드카드를 사용하여 단일 문자가 있는 텍스트 문자열을 일치시킬 수 있습니다.
예를 들어 모든 Xiaomi Redmi 9 변형의 수량을 찾으려면 다음 공식을 사용할 수 있습니다.
=SUMIF(A2:A14,"샤오미 홍미 9?",B2:B14)위의 수식은 "Xiaomi Redmi 9"라는 단어 뒤에 단일 문자가 오는 텍스트 문자열을 찾고 해당하는 합계 수량 번호.
물결표(~) 와일드카드
실제 물음표(?) 또는 별표(*) 문자를 일치시키려면 수식의 조건 부분에서 와일드카드 앞에 물결표(~) 문자를 삽입하십시오.
끝에 별표 기호가 있는 해당 문자열을 사용하여 열 B의 수량을 추가하려면 아래 공식을 입력하십시오.
=SUMIF(A2:A14,"삼성 갤럭시 V~*",B2:B14)
같은 행의 A열에 물음표(?)가 있는 B열의 수량을 추가하려면 다음 공식을 시도하십시오.
=SUMIF(A2:A14,"~?",B2:B14)
날짜 기준이 있는 SUMIF 함수
SUMIF 함수는 또한 날짜 기준에 따라 조건부로 값을 합산하는 데 도움이 될 수 있습니다(예: 특정 날짜에 해당하는 숫자, 날짜 이전 또는 날짜 이후). 날짜 값과 함께 비교 연산자를 사용하여 숫자 합계를 위한 날짜 기준을 만들 수도 있습니다.
날짜는 Google 시트 지원 날짜 형식으로 입력하거나 날짜가 포함된 셀 참조로 입력하거나 DATE() 또는 TODAY()와 같은 날짜 함수를 사용하여 입력해야 합니다.
이 예제 스프레드시트를 사용하여 날짜 기준이 있는 SUMIF 함수가 작동하는 방식을 보여줍니다.
위의 데이터 세트에서 2019년 11월 29일(<=) 또는 그 이전에 발생한 판매 금액을 합산하려는 경우 다음 방법 중 하나로 SUMIF 함수를 사용하여 해당 판매 수를 추가할 수 있습니다.
=SUMIF(C2:C13,"<=2019년 11월 29일",B2:B13)위 수식은 C2에서 C13까지의 각 셀을 확인하고 2019년 11월 29일(2019년 11월 29일) 또는 그 이전의 날짜가 포함된 셀에 대해서만 일치합니다. 그런 다음 B2:B13 셀 범위에서 일치하는 셀에 해당하는 판매 금액을 합산하고 결과를 E3 셀에 표시합니다.
날짜는 '2019년 11월 29일', '2019년 11월 29일' 또는 '2019년 11월 29일' 등과 같이 Google 스프레드시트에서 인식하는 모든 형식으로 수식에 제공될 수 있습니다. 날짜 값을 기억하고 연산자는 반드시 항상 큰따옴표로 묶습니다.
직접 날짜 값 대신 기준에서 DATE() 함수를 사용할 수도 있습니다.
=SUMIF(C2:C13,"<="&DATE(2019,11,29),B2:B13)
또는 수식의 기준 부분에서 날짜 대신 셀 참조를 사용할 수 있습니다.
=SUMIF(C2:C13,"<="&E2,B2:B13)
오늘 날짜를 기준으로 판매 금액을 합산하려면 기준 인수에 TODAY() 함수를 사용할 수 있습니다.
예를 들어 오늘 날짜의 모든 판매 금액을 합산하려면 다음 공식을 사용하십시오.
=수미프(C2:C13,오늘(),B2:B13)
비어 있거나 비어 있지 않은 셀이 있는 SUMIF 함수
경우에 따라 동일한 행에 비어 있거나 비어 있지 않은 셀이 있는 셀 범위의 숫자를 합산해야 할 수도 있습니다. 이러한 경우 SUMIF 함수를 사용하여 셀이 비어 있는지 여부를 기준으로 값을 합산할 수 있습니다.
공백인 경우 합계
Google 스프레드시트에는 빈 셀을 찾는 두 가지 기준이 있습니다: "" 또는 "=".
예를 들어, C 열에 길이가 0인 문자열(시각적으로 비어 있는 것처럼 보임)을 포함하는 모든 판매 금액을 합산하려면 공식에서 사이에 공백 없이 큰따옴표를 사용합니다.
=SUMIF(C2:C13,"",B2:B13)
열 C의 완전한 빈 셀과 열 B의 모든 판매 금액을 합산하려면 "="를 기준으로 포함합니다.
=SUMIF(C2:C13,"=",B2:B13)
공백이 아닌 경우 합계:
값(비어 있지 않음)이 포함된 셀의 합계를 구하려면 수식의 기준으로 ""를 사용할 수 있습니다.
예를 들어, 날짜가 포함된 총 판매액을 얻으려면 다음 공식을 사용하십시오.
=SUMIF(C2:C13,"",B2:B13)
OR 논리가 있는 여러 기준을 기반으로 하는 SUMIF
지금까지 SUMIF 함수는 단일 기준에 따라 숫자를 합산하도록 설계되었지만 Google 스프레드시트의 SUMIF 함수를 사용하면 여러 기준에 따라 값을 합산하는 것이 가능합니다. OR 논리가 있는 단일 수식에서 둘 이상의 SUMIF 함수를 결합하여 수행할 수 있습니다.
예를 들어 지정된 범위(B2:B13)에서 '서부' 지역 또는 '남부' 지역(OR 논리)의 판매 금액을 합산하려면 다음 공식을 사용합니다.
=SUMIF(C2:C13,"서쪽",B2:B13)+SUMIF(C2:C13,"남쪽",B2:B13)이 수식은 조건 중 하나 이상이 TRUE일 때 셀을 합산합니다. 따라서 'OR 논리'로 알려져 있습니다. 또한 모든 조건이 충족될 때 값을 합산합니다.
수식의 첫 번째 부분은 'West' 텍스트에 대해 C2:C13 범위를 확인하고 일치 항목이 충족되면 B2:B13 범위의 값을 합산합니다. 초 부분은 동일한 범위 C2:C13에서 텍스트 값 'South'를 확인한 다음 동일한 sum_range B2:B13에서 일치하는 텍스트와 값을 합산합니다. 그런 다음 두 합계가 함께 추가되어 셀 E3에 표시됩니다.
하나의 기준만 충족되는 경우 해당 합계 값만 반환합니다.
하나 또는 두 개의 기준 대신 여러 기준을 사용할 수도 있습니다. 그리고 여러 기준을 사용하는 경우 수식에 직접 값을 작성하는 대신 셀 참조를 기준으로 사용하는 것이 좋습니다.
=SUMIF(C2:C13,E2,B2:B13)+SUMIF(C2:C13,E3,B2:B13)+SUMIF(C2:C13,E4,B2:B13)
OR 논리가 있는 SUMIF는 지정된 기준 중 하나 이상이 충족될 때 값을 추가하지만 지정된 모든 조건이 충족될 때만 값을 합산하려면 새 형제 SUMIFS() 함수를 사용해야 합니다.
Google 스프레드시트의 SUMIFS 함수(다중 기준)
SUMIF 함수를 사용하여 여러 기준에 따라 값을 합산하면 수식이 너무 길고 복잡해질 수 있으며 실수하기 쉽습니다. 게다가 SUMIF를 사용하면 조건 중 하나가 TRUE일 때 단일 범위에서만 값을 합산할 수 있습니다. SUMIFS 기능이 필요한 곳입니다.
SUMIFS 함수는 하나 이상의 범위에서 여러 일치 기준에 따라 값을 합산하는 데 도움이 됩니다. 그리고 AND 논리에서 작동합니다. 즉, 주어진 모든 조건이 충족될 때만 값을 합산할 수 있습니다. 하나의 조건이 거짓이더라도 결과적으로 '0'을 반환합니다.
SUMIFS 함수 구문 및 인수
SUMIFS 함수의 구문은 다음과 같습니다.
=SUMIFS(합계 범위, 기준 범위1, 기준1, [기준 범위2, ...], [기준2, ...])어디에,
- 합계 범위 – 모든 조건이 충족될 때 합을 구하려는 값이 포함된 셀 범위입니다.
- 기준_범위1 – 기준1을 확인하는 셀의 범위입니다.
- 기준1 – criteria_range1에 대해 확인해야 하는 조건입니다.
- criteria_range2, 기준2, …– 평가할 추가 범위 및 기준. 수식에 더 많은 범위와 조건을 추가할 수 있습니다.
다음 스크린샷의 데이터 세트를 사용하여 SUMIFS 함수가 다양한 기준으로 작동하는 방법을 보여줍니다.
텍스트 조건이 있는 SUMIFS
서로 다른 범위의 서로 다른 두 텍스트 기준에 따라 값을 합산할 수 있습니다. 예를 들어 배송된 텐트 아이템의 총 판매액을 알고 싶다고 가정해보자. 이를 위해 다음 공식을 사용하십시오.
=SUMIFS(D2:D13,A2:A13,"텐트",C2:C13,"배송됨")이 공식에는 "텐트"와 "배송됨"이라는 두 가지 기준이 있습니다. SUMIFS 함수는 범위 A2:A13(criteria_range1)에서 항목 'Tent'(criteria1)를 확인하고 범위 C2:C13(criteria_range2)에서 'Delivered'(기준 2) 상태를 확인합니다. 두 조건이 모두 충족되면 셀 범위 D2:D13(sum_range)의 해당 값을 합산합니다.
숫자 기준 및 논리 연산자가 있는 SUMIFS
조건 연산자를 사용하여 SUMIFS 함수에 대한 숫자로 조건을 생성할 수 있습니다.
캘리포니아 주(CA)에서 5개 이상의 수량의 총 판매량을 찾으려면 다음 공식을 사용하십시오.
=SUMIFS(E2:E13,D2:D13,">5",B2:B13,"CA")이 공식에는 ">5" 및 "CA"의 두 가지 조건이 있습니다.
이 공식은 D2:D13 범위에서 5보다 큰 수량(Qty)을 확인하고 B2:B13 범위에서 'CA' 상태를 확인합니다. 그리고 두 조건이 모두 충족되면(같은 행에 있음을 의미) E2:E13의 금액을 합산합니다.
날짜 기준이 있는 SUMIFS
SUMIFS 기능을 사용하면 동일한 범위 및 다른 범위의 여러 조건을 확인할 수도 있습니다.
2021년 5월 31일 이후 및 2021년 10월 6일 날짜 이전에 배송된 품목의 총 판매 금액을 확인하려는 경우 다음 공식을 사용합니다.
=SUMIFS(E2:E13,D2:D13,">"&G1,D2:D13,"<"&G2,C2:C13,G3)위 수식에는 31/5/2021,10/5/2021 및 배달됨의 세 가지 조건이 있습니다. 직접적인 날짜 및 텍스트 값을 사용하는 대신 해당 기준이 포함된 셀을 참조했습니다.
수식은 동일한 범위 D2:D13에서 2021년 5월 31일(G1) 이후 날짜와 2021년 6월 6일(G2) 이전 날짜를 확인하고 두 날짜 사이의 '배송됨' 상태를 확인합니다. 그런 다음 E2:E13 범위에서 관련 금액을 합산합니다.
비어 있는 셀과 비어 있지 않은 셀이 있는 SUMIFS
경우에 따라 해당 셀이 비어 있는지 여부에 따라 값의 합을 찾고 싶을 수 있습니다. 그렇게 하려면 이전에 논의한 세 가지 기준 중 하나를 사용할 수 있습니다. "=", "" 및 "".
예를 들어, 배송 날짜가 아직 확인되지 않은 '텐트' 항목(빈 셀)의 수량만 합산하려는 경우 "=" 기준을 사용할 수 있습니다.
=SUMIFS(D2:D13,A2:A13,"텐트",C2:C13,"=")수식은 A 열에서 '텐트' 항목(기준 1)을 찾고 C 열에서 해당 공백 셀(기준 2)을 찾은 다음 D 열에서 해당 금액을 합산합니다. "="는 완전히 비어 있는 셀을 나타냅니다.
배송일이 확정된 '텐트' 아이템의 총 수량(빈 셀 아님)을 찾으려면 ""를 기준으로 사용하십시오.
=SUMIFS(D2:D13,A2:A13,"텐트",C2:C13,"")이 공식에서 ""를 "="로 바꿨습니다. 열 C에서 비어 있지 않은 셀이 있는 텐트 항목의 합계를 찾습니다.
OR 논리가 있는 SUMIFS
SUMIFS 함수는 AND 논리에서 작동하므로 모든 조건이 충족될 때만 합산됩니다. 그러나 기준 중 하나가 충족될 때 여러 기준을 기반으로 가치를 합산하려면 어떻게 해야 할까요? 트릭은 여러 SUMIFS 함수를 사용하는 것입니다.
예를 들어 상태가 '주문됨'일 때 '자전거 랙' 또는 '백팩'에 대한 판매 금액을 합산하려면 다음 공식을 시도하십시오.
=SUMIFS(D2:D13,A2:A13,"자전거 랙",C2:C13,"주문됨") +SUMIFS(D2:D13,A2:A13,"백팩",C2:C13,"주문됨")첫 번째 SUMIFS 함수는 두 가지 기준 "Bike rack" 및 "Ordered"를 확인하고 열 D의 금액 값을 합산합니다. 그런 다음 두 번째 SUMIFS는 두 가지 기준 "Backpack" 및 "Ordered"를 확인하고 열 D의 금액 값을 합산합니다. , 두 합계가 함께 더해지고 F3에 표시됩니다. 간단히 말해서 이 공식은 '자전거 랙'이나 '백팩'을 주문했을 때의 합계입니다.
이것이 Google 스프레드시트의 SUMIF 및 SUMIFS 기능에 대해 알아야 할 모든 것입니다.