1일1함수 (31) SUMIF함수 : 조건에 맞는 셀의 값을 더하기, 절대참조와 상대참조

지난시간에 내어드린 문제의 답을 먼저 알려드리겠습니다.
문제는 sheet1 부터 sheet10사이에 있는 모든 시트에서 B2:B11의 범위에 있는 모든 셀의 값을 더하는 수식을 구하는 것이었습니다. 답은 아래와 같습니다.

=SUM(Sheet1:Sheet10!B2:B11)

오늘은 SUMIF함수입니다.

SUMIF(range, criteria, [sum_range])

• range  조건을 적용할 셀 범위로서 필수 항목입니다. 각 범위의 셀은 숫자나 이름, 배열 또는 숫자가 들어 있는 참조여야 합니다. 공백과 텍스트 값은 무시됩니다.
• criteria  추가할 셀을 정의하는 숫자, 식, 셀 참조, 텍스트 또는 함수 형식의 조건으로서 필수 항목입니다. 예를 들어 32, ">32", B5, 32, "32", "사과", 오늘() 등으로 criteria를 표시할 수 있습니다.

   중요   텍스트 조건이나 논리 기호 또는 수학 기호가 포함된 조건은 큰따옴표(")로 묶어야 합니다. 조건이 숫자인 경우에는 큰따옴표가 필요 없습니다.

• sum_range  range 인수에 지정된 것과 다른 셀을 더하려는 경우 실제로 더할 셀로서 선택 항목입니다. sum_range 인수를 지정하지 않으면 range 인수에 지정된 셀(조건이 적용되는 셀)이 더해집니다.

• criteria 인수로 와일드카드 문자인 물음표(?)와 별표(*)를 사용할 수 있습니다. 물음표는 문자 하나에 해당하고, 별표는 개수에 상관없는 일련의 문자에 해당합니다. 실제 물음표나 별표를 찾으려면 문자 앞에 물결표(~)를 입력합니다.
  

이전에 COUNTIF에서 다루었던 주의점들이 여기서도 보입니다.
- 같은 수를 조건으로 주면 따옴표가 필요없지만, 부등호로 비교를하면 따옴표를 넣어줘야한다는 점
SUM(A1:A5,100)         A1:A5범위중 100와 같은 값을 가지고 있는 셀의 값의 합
SUM(A1:A5,">100")     A1:A5범위중 100보다 큰 값을 가지고 있는 셀의 값의 합

- 숫자를 넣지 않고 숫자가 들어있는 셀을 참조하게 만들면 &를 붙여줘야한다는 점
SUM(A1:A5,">"&C1)         A1:A5범위중 C1셀의 값보다 큰 값을 가지고 있는 셀의 값의 합

- 와일드카드 (*,?)를 사용할 수 있다는 점

SUMIF(B2:B7,"박*",C2:C7)

B2:B7의 셀중 "박"으로 시작하는 모든 행의 C열의 합계

주의할 점이 하나 더 보입니다. sum_range라는 인수입니다. 꼭 써야하는 인수가 아니라, 쓸 수도 안 쓸 수도 있는데 쓰게 되면 이 sum_range부분의 합계를 내야하고, 쓰지 않으면 range부분의 합계를 내야한다는 의미입니다. 아래의 예를 보시겠습니다.

이렇게 국어와 영어 성적표가 있다고 합시다. 총점순으로 정렬했기때문에 반별 점수가 어느 반이 제일 높은지 알기 힘듭니다.

이때 SUMIF함수를 쓰면 각 과목의 반별 점수 합계를 알 수 있습니다.

1반의 국어점수 합계를 나타내는 수식을 보겠습니다.

=SUMIF($A$2:$A$16,$G2,C$2:C$16)

=SUMIF(	다음 조건이 맞으면 합계를 냅니다
$A$2:$A$16,	A2:A16의 범위중에서	"반 "을 표시한 열에
$G2,	G2셀의 값과 같은 값을 가지는셀들의	1반이라고 써 있는 학생들의
C$2:C$16)	C2:C16의 범위에 있는 값	국어점수의 합계

만약 다음과 같이 수식을 쓴다면 결과값은 무엇이 될까요?

=SUMIF($A$2:$A$16,$G2)

답은 3이 나옵니다. A2:A16의 범위중에서 1과 같은 값을 가지고 있는 셀들의 A2:A16에 있는 값들의 합을 구하라는 의미가 되기 때문입니다. (1이라는 숫자는 A2:A16의 범위에서 3개가 있지요.)


그리고, 수식을 쓰실때 항상 주의하실 점은, 엑셀에서는 수식을 하나만 쓰고 끝나는 경우가 별로 없기에, 아래쪽으로든 오른쪽으로던 끌어서 채우는 것을 염두에 두고 절대참조와 상대참조를 구별하여 붙이셔야한다는 것입니다.

여기서는 A2:A16의 셀은 행과 열 둘 다 절대참조로 되어있습니다만,
G2셀은 G열이 절대, 2행은 상대참조로 되어있고
C2:C16의 범위는 C열이 상대, 2:16행이 절대참조로 되어있습니다.
그 이유는 오른쪽과 아래쪽으로 끌기해보시면 알 수 있습니다.

오른쪽으로 끌면 C2:C16의 범위가 D2:D16으로 바뀌어야하기에 C열에 상대참조를 써야하고

아래쪽으로 끌면 G2가 G3으로 바뀌어야하기에 2행에 상대참조를 써야합니다.


마지막으로, 지난시간에 SUM함수에서 편리하게 사용했던 여러시트를 참조하는 방법이 SUMIF함수에서도 쓸 수 있는지 알아봅시다.
3월,4월,5월의 시트를 똑같이 만들고, TOTAL시트에서 다음과 같이 수식을 만들어 봅니다.

=SUMIF(3월:5월!$A$2:$A$16,A2,3월:5월!$C$2:$C$16)

그러면 보시는 것처럼 #VALUE!  오류가 뜹니다. 여러시트를 참조하여 SUMIF함수를 쓸 수 있다면 좋았을텐데, 심히 안타까운 일이 아닐 수 없습니다. ^^;

2009/04/05 - [컴퓨터/엑셀] - 1일1함수 (11) COUNTIF 함수
2009/05/10 - [컴퓨터/엑셀] - 1일1함수 (30) SUM 함수 : 여러 시트의 합계내기