엑셀 sumif 함수로 조건별 금액 합계 구하는 방법
얼마 전 조립 PC 부품값을 엑셀에 적다가 SSD에 쓴 돈만 따로 보고 싶어졌습니다. 부품이 몇 개 없을 때는 눈으로 더해도 되지만, 주문 내역이 늘어나면 빠뜨리기 쉽습니다. 이럴 때 엑셀 sumif 함수는 지정한 조건에 맞는 행의 숫자만 더해 줍니다.
부품 이름을 기준으로 금액 더하기
A2부터 A7까지 부품 종류, B2부터 B7까지 결제 금액이 있다고 해보죠. A열에는 CPU, 메모리, SSD, SSD, 파워, SSD가 차례로 들어 있고, B열 금액은 각각 320000원, 98000원, 110000원, 145000원, 89000원, 76000원입니다. SSD 세 건의 합계는 331000원입니다.
빈 셀에 =SUMIF(A2:A7,"SSD",B2:B7)을 입력하면 같은 값이 나옵니다. 괄호 안의 첫 번째 범위는 조건을 확인할 곳, 두 번째 항목은 찾을 조건, 세 번째 범위는 실제로 더할 숫자가 있는 곳입니다. A열에서 SSD를 찾은 뒤 같은 행의 B열 금액을 더하는 방식입니다.
조건을 확인할 범위와 합계를 낼 범위는 같은 행을 가리키도록 잡는 게 중요합니다. A2:A7을 검사하면서 B3:B8을 합산하도록 지정하면 부품 이름과 금액이 한 줄씩 어긋납니다. 수식 자체보다 범위의 시작 행을 먼저 확인하는 편이 빠릅니다.
조건을 셀에 넣으면 수식을 다시 쓸 일이 줄어든다
부품 종류를 바꿔 가며 확인한다면 수식 안에 SSD를 직접 적는 것보다 조건 셀을 따로 두는 편이 편합니다. D2에 SSD를 입력하고 =SUMIF(A2:A7,D2,B2:B7)을 쓰면 됩니다. D2를 CPU로 바꾸면 결과는 320000원, 파워로 바꾸면 89000원으로 바뀝니다.
여러 종류의 합계를 나란히 표시할 때도 같은 방식입니다. D2부터 D4까지 CPU, SSD, 파워를 적고 E2에 =SUMIF($A$2:$A$7,D2,$B$2:$B$7)을 넣은 다음 아래로 복사합니다. 달러 표시가 붙은 부품 범위와 금액 범위는 고정되고, 조건 셀 D2만 D3, D4로 내려갑니다. 달러 표시를 빼면 수식을 복사할 때 검사 범위까지 함께 밀릴 수 있습니다.
여기서 SSD라는 글자를 수식에 직접 넣을 때는 큰따옴표가 필요하지만, D2처럼 셀을 참조할 때는 큰따옴표를 붙이지 않습니다. =SUMIF(A2:A7,"D2",B2:B7)이라고 쓰면 D2 셀의 내용이 아니라 D2라는 글자를 찾습니다.
금액 조건과 부분 일치도 쓸 수 있다
10만 원을 넘는 결제 금액만 더하려면 =SUMIF(B2:B7,">100000")을 입력합니다. 합계를 낼 범위를 생략했으므로 조건을 검사한 B열 숫자 자체를 더합니다. 예시에서는 320000원, 110000원, 145000원이 해당해 결과가 575000원입니다. 10만 원도 포함하려면 기호를 >=로 바꿔 ">=100000"이라고 적습니다.
기준 금액이 D2에 들어 있다면 =SUMIF(B2:B7,">"&D2)처럼 비교 기호와 셀 참조를 &로 이어야 합니다. ">D2"라고 쓰면 D2의 숫자를 읽지 못합니다. 견적 상한선을 바꿔 가며 확인할 때 이 차이 때문에 결과가 0으로 나오는 경우가 꽤 있습니다.
부품 이름이 SSD 1TB, SSD 2TB처럼 적혀 있다면 =SUMIF(A2:A7,"SSD*",B2:B7)을 사용할 수 있습니다. 별표는 뒤에 어떤 글자가 오든 허용한다는 뜻입니다. 다만 외장 SSD처럼 앞에 다른 글자가 붙은 항목까지 포함하려면 "*SSD*"가 필요합니다. 이 방식은 셀에 SSD라는 글자가 포함돼 있는지만 확인하므로, 품목 구분을 정확히 해야 하는 장부라면 이름을 일정하게 입력하는 편이 낫습니다.
합계가 0이거나 예상과 다를 때 확인할 곳
먼저 조건 셀의 띄어쓰기를 확인합니다. 화면에는 비슷해 보여도 SSD와 SSD 뒤에 공백이 붙은 값은 다르게 취급될 수 있습니다. 판매처 주문 내역을 복사해 붙였다면 앞뒤 공백이 섞이기 쉬워서, 해당 셀을 직접 눌러 확인하는 게 좋습니다.
다음은 금액 셀입니다. 숫자처럼 보이지만 실제로는 문자로 저장된 금액이 섞이면 합계에서 빠질 수 있습니다. 예를 들어 셀 값에 원이라는 글자까지 함께 입력했다면 숫자 110000과는 다릅니다. 금액은 숫자만 입력하고 원 표시는 셀 표시 형식으로 붙여 두면 계산할 때 덜 헷갈립니다.
조건이 둘 이상인지 확인합니다. 부품이 SSD이면서 구매처도 특정 판매처인 금액을 구하려면 SUMIF 하나보다 SUMIFS가 맞습니다. 저는 조립 견적표를 만들 때 부품 종류 하나만 볼 때는 SUMIF를 쓰고, 구매처나 주문 상태까지 걸러야 할 때는 SUMIFS로 넘어갑니다. 조건이 늘어난 걸 모르고 수식 하나에 억지로 끼워 넣는 것보다 시트도 읽기 쉽고 결과를 검산하기도 편합니다.
