여러 조건이있는 Excel SUMIFS 및 SUMIF – 수식 예

이 자습서에서는 구문 및 SUMIFS 함수의 차이점을 설명합니다. Excel 2019, 2016, 2013, 2010, 2007, 2003 이하에서 여러 AND / OR 기준으로 값을 합산하기위한 여러 수식 예제를 제공합니다.

모두가 알고 있듯이 Microsoft Excel은 배열을 제공합니다. 데이터로 다양한 계산을 수행하는 함수 몇 가지 기사 전에 우리는 각각 단일 조건과 여러 조건을 기반으로 세포를 계산하도록 설계된 COUNTIF 및 COUNTIFS를 살펴 보았습니다. 지난주 우리는 지정된 기준을 충족하는 값을 추가하는 Excel SUMIF를 다루었습니다. 이제 여러 기준에 따라 값을 합산 할 수있는 Excel SUMIFS 인 SUMIF의 복수 버전을 살펴볼 시간입니다.

SUMIF 함수에 익숙한 사용자는 SUMIF 함수를 SUMIFS로 변환하는 데 추가 비용이 필요하다고 생각할 수 있습니다. “S”및 몇 가지 추가 기준. 이것은 매우 논리적으로 보이지만 Microsoft를 다룰 때 항상 “논리적”인 것은 아닙니다.)

SUMIF 함수-구문 & 사용법
SUMIFS 함수-구문 & 사용법
Excel에서 SUMIFS를 사용하는 방법 (AND 논리)
여러 기준이있는 Excel SUMIF (OR 논리)
여러 OR 조건이있는 Excel SUMIFS
배열 수식에 Excel SUM 사용

Excel SUMIF 함수-구문 & 사용법

SUMIF 함수는 단일 기준에 따라 조건부로 값을 합산하는 데 사용됩니다. 이전 기사에서 SUMIF 구문에 대해 자세히 논의 했으므로 이제 간단히 요약 해 보겠습니다.

SUMIF (range, criteria,)

range-기준에 따라 평가할 셀 범위이며 필수입니다.
criteria-충족해야하는 조건, 필수입니다.
sum_range-조건이 충족되면 합계 할 셀입니다. 선택 사항입니다.

보시다시피 Excel SUMIF 함수의 구문은 하나의 조건 만 허용합니다. 그리고 여전히 Excel SUMIF를 사용하여 여러 기준으로 값을 합산 할 수 있다고 말합니다. 어떻게 그렇게 될수 있니? 다음 예에서 설명하는 것처럼 여러 SUMIF 함수의 결과를 추가하고 배열 기준과 함께 SUMIF 수식을 사용합니다.

Excel SUMIFS 함수-구문 & 사용법

Excel에서 SUMIFS를 사용하여 여러 기준에 따라 값의 조건부 합계를 찾습니다. SUMIFS 함수는 Excel 2007에서 도입되었으므로 모든 최신 버전의 Excel 2019, 2016, 2013, 2010 및 2007에서 사용할 수 있습니다.

SUMIF와 비교하여 SUMIFS 구문은 약간 더 복잡합니다. :

SUMIFS (sum_range, criteria_range1, criteria1,,…)

처음 3 개의 인수는 필수이며 추가 범위 및 관련 기준은 선택 사항입니다.

sum_range-합계 할 하나 이상의 셀, 필수입니다. 단일 셀, 셀 범위 또는 명명 된 범위가 될 수 있습니다. 숫자가있는 셀만 합산됩니다. 공백 및 텍스트 값은 무시됩니다.
criteria_range1-관련 기준에 의해 평가 될 첫 번째 범위이며 필수입니다.
criteria1-충족해야하는 첫 번째 조건이며 필수입니다. 숫자, 논리식, 셀 참조, 텍스트 또는 다른 Excel 함수의 형식으로 기준을 제공 할 수 있습니다. 예를 들어 10, “> = 10”, A1, “cherries”또는 TODAY ()와 같은 기준을 사용할 수 있습니다.
criteria_range2, criteria2, …-추가 범위 및 관련 기준이며 선택 사항입니다. SUMIFS 수식에서 최대 127 개의 범위 / 기준 쌍을 사용할 수 있습니다.

중요! SUMIFS 함수는 AND 로직과 함께 작동합니다. 즉, 합계 범위의 셀은 지정된 기준을 모두 충족하는 경우에만 합산됩니다 (해당 셀에 대한 모든 기준이 참).

그리고 이제 let ” 두 가지 조건이있는 Excel SUMIFS 공식을 살펴 봅니다. 다른 공급자의 과일 운송물을 나열하는 테이블이 있다고 가정합니다. A 열에는 과일 이름, B 열에는 공급자 이름, C 열에는 수량이 있습니다. 당신이 원하는 것은 주어진 과일과 공급자와 관련된 양의 합계를 찾는 것입니다. Pete에서 제공하는 모든 사과.

“새로운 것을 배울 때”항상 간단한 것부터 시작하는 것이 좋습니다. 따라서 먼저 SUMIFS 공식에 대한 모든 인수를 정의하겠습니다.

sum_range-C2 : C9
criteria_range1-A2 : A9
criteria1- “apples”
criteria_range2-B2 : B9
criteria2- “Pete”

이제 위의 매개 변수를 조합하면 다음 SUMIFS 공식을 얻습니다.

=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

다듬기 수식을 추가하면 텍스트 조건 “apples”및 “Pete”를 셀 참조로 바꿀 수 있습니다.이 경우 다른 공급 업체의 다른 과일 수량을 계산하기 위해 공식을 변경할 필요가 없습니다.

=SUMIFS(C2:C9, A2:A9, F1, B2:B9, F2)

Excel에서 SUMIFS 및 SUMIF 사용-기억해야 할 사항

이 자습서의 목적은 여러 조건으로 값을 합산하는 가능한 모든 방법을 다루는 것이므로 Excel SUMIFS 및 SUMIF 두 함수를 사용하여 수식 예를 설명합니다. 여러 기준이 있습니다. 올바르게 사용하려면이 두 기능의 공통점과 차이점을 명확하게 이해해야합니다.

공통 부분은 분명하지만-유사한 목적지와 매개 변수-차이점 매우 중요하지만 그렇게 명확하지는 않습니다.

1. 인수 순서

Excel SUMIF 및 SUMIFS 함수에서는 인수 순서가 다릅니다. 특히 sum_range는 SUMIFS에서는 매개 변수이지만 SUMIF에서는 3 위입니다.

첫눈에 Microsoft가 사용자의 학습 곡선을 일부러 복잡하게 만드는 것처럼 보일 수 있습니다. 어, 자세히 살펴보면 그 뒤에있는 이유를 볼 수 있습니다. 요점은 sum_range가 SUMIF에서 선택 사항이라는 것입니다. 생략해도 문제 없습니다. SUMIF 공식은 범위 (첫 번째 매개 변수)의 값을 합산합니다.

SUMIFS에서 sum_range는 매우 중요하고 의무적이며 이것이 먼저 오는 이유입니다. 아마도 Microsoft 직원들은 10 번째 또는 100 번째 범위 / 기준 쌍을 추가 한 후 누군가 합계 할 범위를 지정하는 것을 잊을 수 있다고 생각했습니다.)

2. 합계 범위와 기준 범위는 같은 크기 여야합니다.

SUMIF 함수에서 sum_range 인수는 왼쪽 상단 셀이 오른쪽에있는 한 반드시 범위 인수와 같은 크기 일 필요는 없습니다. Excel SUMIFS에서 각 criteria_range는 sum_range 매개 변수와 동일한 수의 행과 열을 포함해야합니다.

예를 들어, =SUMIF(A2:A9,F1,C2:C18)는 Excel에서 다음을 고려하므로 올바른 결과를 반환합니다. sum_range 인수의 맨 왼쪽 상단 셀만 (이 예에서는 C2, 맞습니다) 범위 인수에 포함 된만큼의 열과 행을 포함합니다.

좋아, 충분한 전략 (예 : 이론) , 전술에 대해 알아 보겠습니다 (예 : 수식 예 🙂

Excel에서 SUMIFS 사용 방법-수식 예

잠시 전에 두 개의 텍스트가있는 간단한 SUMIFS 수식에 대해 논의했습니다. 같은 방식으로 숫자, 날짜, 논리식 및 기타 Excel 함수로 표현되는 여러 기준과 함께 Excel SUMIFS를 사용할 수 있습니다.

예 1. 비교 연산자가있는 Excel SUMIFS

과일 공급 업체 테이블에서 Mike의 모든 납품을 수량 200 개 이상으로 합산하려고한다고 가정합니다. 이렇게하려면 비교 연산자 “greater than o r equal to “(> =) in criteria2 및 다음 SUMIFS 수식을 가져옵니다.

=SUMIFS(C2:C9,B2:B9,"Mike",C2:C9,">=200")

참고. Excel SUMIFS 수식에서 비교 연산자가있는 논리식은 항상 큰 따옴표 ( “”)로 묶어야합니다.

같은 연산자 인 Excel SUMIF 함수를 논의 할 때 가능한 모든 비교 연산자를 자세히 다뤘습니다. SUMIFS 기준에서 사용할 수 있습니다. 예를 들어 다음 수식은 C2 : C9 셀에서 200보다 크거나 같고 300보다 작거나 같은 모든 값의 합계를 반환합니다.

=SUMIFS(C2:C9, C2:C9,">=200", C2:C9,"<=300")

예 2. 날짜와 함께 Excel SUMIFS 사용

현재 날짜를 기준으로 여러 기준으로 값을 합산하려면 SUMIFS에서 TODAY () 함수를 사용합니다. 아래에 설명 된대로 기준. 다음 수식은 C 열의 해당 날짜가 오늘을 포함하여 지난 7 일 이내 인 경우 D 열의 값을 합산합니다.

=SUMIFS(D2:D10, C2:C10,">="&TODAY()-7, C2:C10,"<="&TODAY())

비슷한 방식으로 Excel SUMIF 함수를 사용하여 주어진 날짜 범위의 값을 합산 할 수 있습니다. 예를 들어 다음 SUMIFS 수식은 B 열의 날짜가 2014 년 10 월 1 일에서 2014 년 10 월 31 일 사이에 해당하는 경우 C2 : C9 셀의 값을 추가합니다.

=SUMIFS(C2:C9, B2:B9, ">=10/1/2014", B2:B9, "<=10/31/2014")

이 예 (SUMIF를 사용하여 주어진 날짜 범위의 값을 합산하는 방법)에 설명 된대로 두 SUMIF 함수의 차이를 계산하여 동일한 결과를 얻을 수 있습니다. 그러나 Excel SUMIFS는 훨씬 쉽고 이해하기 쉽습니다. 그렇지 않습니까?

예 3. 빈 셀과 비어 있지 않은 셀이있는 Excel SUMIFS

보고서 및 기타 데이터를 분석 할 때 비어 있거나 비어 있지 않은 셀에 해당하는 값을 합산해야하는 경우가 종종 있습니다.

그리고 이제 실제에서 “비어 있음”및 “비어 있지 않은”기준과 함께 SUMIFS 수식을 사용하는 방법을 살펴 보겠습니다. 데이터.

B 열에 주문일이 있고 C 열에 배송일이 있고 수량이 있다고 가정합니다. D 열에서. 아직 배송되지 않은 제품의 총계를 어떻게 찾습니까? 즉, B 열의 비어 있지 않은 셀과 C 열의 빈 셀에 해당하는 값의 합계를 알고 싶습니다.

해결 방법은 다음 두 가지 기준으로 SUMIFS 공식을 사용하는 것입니다.

=SUMIFS(D2:D10, B2:B10,"<>", C2:C10,"=")

여러 OR 기준과 함께 Excel SUMIF 사용

이 자습서의 시작 부분에서 언급했듯이 SUMIFS 함수는 AND 논리로 설계되었습니다. 그러나 여러 OR 기준으로 값을 합산해야하는 경우 (예 : 조건 중 하나 이상이 충족되는 경우)

예 1. SUMIF + SUMIF

가장 간단한 해결책은 합계하는 것입니다. 여러 SUMIF 함수에 의해 반환 된 결과. 예를 들어 다음 수식은 Mike와 John이 제공 한 총 제품을 찾는 방법을 보여줍니다.

=SUMIF(C2:C9,"Mike",D2:D9) + SUMIF(C2:C9,"John",D2:D9)

보시다시피 첫 번째 SUMIF 함수는 “Mike”에 해당하는 수량을 추가하고 다른 SUMIF 함수는 “John”과 관련된 금액을 반환 한 다음이 두 숫자를 추가합니다.

예제 2. SUM & 배열 인수가있는 SUMIF

위의 솔루션은 매우 간단하며 몇 가지 기준. 그러나 여러 OR 조건으로 값을 합산하려는 경우 SUMIF + SUMIF 수식이 엄청나게 커질 수 있습니다. 이 경우 더 나은 방법은 SUMIF 함수에서 배열 기준 인수를 사용하는 것입니다. 이제이 접근 방식을 살펴 보겠습니다.

모든 조건을 쉼표로 구분하여 나열한 다음 쉼표로 구분 된 결과 목록을 기술적으로 배열이라고하는 {중괄호}로 묶을 수 있습니다.

3 개의 값으로 구성된 배열 인수는 SUMIF 수식이 세 개의 개별 결과를 반환하도록하지만 단일 셀에 수식을 작성하기 때문에 첫 번째 결과 만 반환합니다. 즉 John이 제공 한 총 제품 수입니다. .이 배열 기준 접근 방식이 작동하려면 한 가지 더 작은 트릭을 사용해야합니다. 다음과 같이 SUM 함수에 SUMIF 수식을 묶어야합니다.

=SUM(SUMIF(C2:C9, {"John","Mike","Pete"} , D2:D9))

보시다시피 배열 기준은 SUMIF + SUMIF에 비해 수식을 훨씬 더 간결하게 만들고 다음과 같은 값을 추가 할 수 있습니다. 배열에서 좋아합니다.

이 접근 방식은 텍스트 값뿐만 아니라 숫자에도 적용됩니다. 예를 들어 C 열의 공급자 이름 대신 1, 2, 3 등과 같은 공급자 ID가있는 경우 ., 그러면 SUMIF 공식은 다음과 유사합니다.

=SUM(SUMIF(C2:C9, {1,2,3} , D2:D9))

텍스트 값과 달리 숫자는 큰 따옴표로 묶일 필요가 없습니다. 배열 인수

예 3. SUMPRODUCT & SUMIF

경우에 따라 선호하는 방법은 일부 셀에 기준을 나열하는 것입니다. 수식에서 직접 지정하면 주어진 배열의 구성 요소를 곱하고 해당 곱의 합계를 반환하는 SUMPRODUCT 함수와 함께 SUMIF를 사용할 수 있습니다.

=SUMPRODUCT(SUMIF(C2:C9, G2:G4, D2:D9))

G2 : G4가 기준을 포함하는 셀인 경우 아래 스크린 샷에 설명 된 바와 같이 공급 업체의 이름입니다.

물론, 원하는 경우 SUMIF 함수의 배열 기준에 값 나열 :

=SUMPRODUCT(SUMIF(C2:C9, {"Mike","John","Pete"}, D2:D9))

두 수식에서 반환되는 결과는 다음과 같습니다. 스크린 샷에 표시된 것과 동일합니다.

Excel SUMIFS 여러 OR 기준

여러 OR 조건이 아니라 여러 조건 집합을 사용하여 Excel에서 조건부로 값을 합산하려면 SUMIF 대신 SUMIFS를 사용해야합니다. 공식은 우리가 방금 논의한 것과 매우 유사합니다.

평소처럼 예가 요점을 더 잘 설명하는 데 도움이 될 수 있습니다. 과일 공급 업체 표에서 배송일 (열)을 추가해 보겠습니다. E) 10 월에 Mike, John, Pete가 배달 한 총 수량을 찾습니다.

예 1. SUMIFS + SUMIFS

이 접근 방식으로 생성 된 공식에는 많은 반복과 모양이 포함됩니다. 번거롭지 만 이해하기 쉽고 가장 중요한 것은 작동합니다.)

예제 2. SUM & 배열 인수가있는 SUMIFS

저는 SUMIF 예제에서이 접근 방식의 본질을 설명하려고했습니다. 이제 우리는 간단히 해당 공식을 복사하고 인수 순서를 변경하고 (SUMIF와 SUMIFS에서 다름을 기억하는 것처럼) 추가 기준을 추가 할 수 있습니다. 결과 수식은 SUMIFS + SUMIFS보다 더 간결합니다.

=SUM(SUMIFS(D2:D9,C2:C9, {"Mike", "John", "Pete"}, E2:E9,">=10/1/2014", E2:E9, "<=10/31/2014"))

이 수식이 반환하는 결과는 다음과 같습니다. 위 스크린 샷

예 e 3. SUMPRODUCT & SUMIFS

기억 하시겠지만 SUMPRODUCT 접근 방식은 각 기준을 별도의 셀에 입력하는 방식이 이전 두 가지 방식과 다릅니다. 오히려 공식에 직접 지정하십시오. 여러 기준 세트의 경우 SUMPRODUCT 기능이 충분하지 않으며 ISNUMBER 및 MATCH도 사용해야합니다.

따라서 소모품 이름이 셀 H1 : H3에 있고 시작 날짜가 셀 H4에 있고 종료 날짜가 셀 H5에 있다고 가정하면 SUMPRODUCT 수식은 다음과 같은 모양을 갖습니다.

=SUMPRODUCT(--(E2:E9>=H4), --(E2:E9<=H5), --(ISNUMBER(MATCH(C2:C9, H1:H3,0))), D2:D9)

많은 사람들이 SUMPRODUCT 수식에서 이중 대시 (-)를 사용하는 이유를 궁금해합니다. 요점은 Excel SUMPRODUCT는 숫자 값을 제외한 모든 값을 무시하는 반면 수식의 비교 연산자는 숫자가 아닌 부울 값 (TRUE / FALSE)을 반환한다는 것입니다. 이러한 부울 값을 1 “및 0″으로 변환하려면 기술적으로 이중 단항 연산자라고하는 이중 빼기 기호를 사용합니다. 첫 번째 단항은 TRUE / FALSE를 각각 -1/0으로 강제 변환합니다. 두 번째 단항은 값을 부정합니다. 즉 부호를 반전하여 SUMPRODUCT 함수가 이해할 수있는 +1과 0으로 바꿉니다.

위의 설명이 의미가 있기를 바랍니다. 그렇지 않더라도이 경험 법칙을 기억하십시오. SUMPRODUCT 수식에서 비교 연산자를 사용할 때는 이중 단항 연산자 (-)를 사용하십시오.

배열 수식에서 Excel SUM 사용

h2>

기억하고 있듯이 Microsoft는 Excel 2007에서 SUMIFS 함수를 구현했습니다. 누군가 여전히 Excel 2003, 2000 또는 이전 버전을 사용하는 경우 SUM 배열 수식을 사용하여 여러 AND 조건이있는 값을 추가해야합니다. 당연히이 방법은 최신 버전의 Excel 2013-2007에서도 작동하며 SUMIFS 함수의 구식 대응 요소로 간주 될 수 있습니다.

위에서 설명한 SUMIF 수식에서는 이미 배열 인수를 사용했지만 배열 수식을 사용했습니다. 뭔가 다릅니다.

예제 1. Excel 2003 및 이전 버전에서 여러 AND 기준을 사용한 합계

관련된 금액의 합계를 찾은 첫 번째 예로 돌아가 보겠습니다. 주어진 과일 및 공급자에게 :

이미 알고 있듯이이 작업은 일반 SUMIFS 공식 :
=SUMIFS(C2:C9, A2:A9, "apples", B2:B9, "Pete")

이제 “SUMIFS가없는”초기 버전의 Excel에서 동일한 작업을 수행 할 수있는 방법을 살펴 보겠습니다. . 먼저 range = “condition”형식으로 충족되어야하는 모든 조건을 기록합니다. 이 예에는 두 가지 범위 / 조건 쌍이 있습니다.

조건 1 : A2 : A9 = “apples”

조건 2 : B2 : B9 = “Pete”

그런 다음 각각 괄호로 묶인 모든 범위 / 조건 쌍을 “곱하는”SUM 수식을 작성합니다. 마지막 승수는 합계 범위,이 경우 C2 : C9입니다.

=SUM((A2:A9="apples") * ( B2:B9="Pete") * ( C2:C9))

아래 스크린 샷에 표시된 것처럼 공식 최신 Excel 2013 버전에서 완벽하게 작동합니다.

참고. 배열 수식을 입력 할 때 Ctrl + Shift + Enter를 눌러야합니다. 이렇게하면 수식이 {중괄호}로 묶여 배열 수식이 올바르게 입력되었음을 시각적으로 나타냅니다. 중괄호를 수동으로 입력하면 수식이 텍스트 문자열로 변환되어 작동하지 않습니다.

예 2. 최신 Excel 버전의 SUM 배열 수식

도 최신 버전의 Excel에서 SUM 함수의 힘은 과소 평가되어서는 안됩니다. SUM 배열 수식은 단순히 마음의 체조가 아니라 다음 예에서 보여 주듯이 실용적인 가치를 가지고 있습니다.

가정 , B와 C라는 두 개의 열이 있고 C 열의 값이 10보다 크거나 같을 때 C 열이 B 열보다 몇 번 더 큰지 계산해야합니다. 떠오르는 즉각적인 해결책은 SUM을 사용하는 것입니다. 배열 수식 :

=SUM((C1:C10>=10) * (C1:C10>B1:B10))

실용적인 위 공식에 적용? 다른 방식으로 생각해보십시오.)

아래 스크린 샷에 표시된 것과 같은 주문 목록이 있고 주어진 날짜까지 완전히 배송되지 않은 제품 수를 알고 싶다고 가정 해 보겠습니다. Excel의 언어로 번역 된 조건은 다음과 같습니다.

조건 1 : B 열 (주문 된 항목)의 값이 0보다 큽니다

조건 2 : 열의 값 C (배달 됨) 열 B 미만

조건 3 : A 열 D (만기일)의 날짜가 2014 년 11 월 1 일 미만입니다.

세 범위 입력 / 조건 쌍을 함께 사용하면 다음 공식을 얻을 수 있습니다.

=SUM((B2:B10>=0)*(B2:B10>C2:C10)*(D2:D10<G2))

이 자습서에서 설명하는 수식 예제는 Excel SUMIFS 및 SUMIF 함수가 실제로 수행 할 수있는 작업의 표면에 불과했지만 올바른 방향을 가리키는 데 도움이 되었기를 바랍니다. 이제 Excel 통합 문서에서 값을 합산 할 수 있습니다. 고려해야 할 복잡한 조건의 수.

Excel에서 SUMIF를 사용하는 방법-셀을 조건부로 합산하는 수식 예
다양한 기준으로 Excel SUMIFS 및 SUMIF를 사용하는 방법-수식 예
VLOOKUP & Excel에서 SUM 또는 SUMIF 함수를 사용하는 방법
수식 예제가있는 Google 스프레드 시트의 SUMIF
Excel의 COUNTIF-그렇지 않은 경우 계산 공백, 초과, 중복 또는 고유
Excel에서 색상별로 셀을 계산하고 합계하는 방법

여러 조건이있는 Excel SUMIFS 및 SUMIF 사용 방법-수식 예