메뉴
실무 위키›응용 공식›중복 주문번호 금액 한 번만 합계 공식

중복 주문번호 금액 한 번만 합계 공식

지원 버전 자세히 보기
엑셀 2013 이전엑셀 2016엑셀 2019엑셀 2021엑셀 2024M365웹 엑셀

품목별로 반복된 주문 총액을 주문번호마다 한 번씩만 더합니다.

예제파일 다운로드
중복 주문번호 금액 한 번만 합계 공식 예제 미리보기
예제 미리보기 크게 보기

인수 설명

=SUMPRODUCT ( 결제 금액 범위 / COUNTIF ( 주문번호 범위, 주문번호 범위 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
인수구분설명
결제 금액 범위필수각 품목 행에 반복해서 적힌 주문 총액 범위입니다. 같은 주문번호의 금액은 모두 같아야 합니다.
주문번호 범위필수중복 횟수를 확인할 주문번호 범위입니다. 주문번호가 비어 있는 행은 먼저 제거합니다.
D2=SUMPRODUCT($C$2:$C$7/COUNTIF($A$2:$A$7,$A$2:$A$7))
A
B
C
D
E
1
주문번호
품목
결제 금액
중복 제외 합계
2
SO-101
키보드
120,000
350,000
3
SO-101
마우스
120,000
4
SO-102
모니터
80,000
5
SO-103
의자
150,000
6
SO-103
책상
150,000
7
SO-103
조명
150,000
8
한 주문의 총액이 품목 행마다 똑같이 반복된 내역에 사용합니다. 주문번호별 금액이 서로 다르면 주문 총액을 먼저 한 값으로 맞춰야 합니다.
이 공식이 사용하는 함수
SUMPRODUCT 함수 COUNTIF 함수

동작 원리

COUNTIF 함수가 주문번호의 반복 횟수를 구하고, SUMPRODUCT 함수가 그 횟수로 나눈 금액을 모두 합칩니다.

예제 시트에서 중복을 제외한 결제 금액을 구하는 D2 셀의 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.

1

주문번호별 반복 횟수를 구합니다

SO-101은 두 번, SO-102는 한 번, SO-103은 세 번 나오므로 각 행에 해당 횟수가 돌아옵니다.

=SUMPRODUCT ( $C$2:$C$7 / COUNTIF ( $A$2:$A$7, $A$2:$A$7 ) )
= 2, 2, 1, 3, 3, 3
2

반복 금액을 횟수만큼 나눕니다

두 번 반복된 120,000원은 행마다 60,000원으로, 세 번 반복된 150,000원은 행마다 50,000원으로 바뀝니다.

= ( 120,000, 120,000, 80,000, 150,000, 150,000, 150,000 ) / ( 2, 2, 1, 3, 3, 3 )
= 60,000, 60,000, 80,000, 50,000, 50,000, 50,000
3

나눈 금액을 모두 더합니다

나뉜 값을 다시 합치면 각 주문 총액이 정확히 한 번씩만 들어가 350,000원이 됩니다.

=SUMPRODUCT ( 60,000, 60,000, 80,000, 50,000, 50,000, 50,000 )
= 350,000
댓글 0
아직 댓글이 없습니다. 첫 번째 댓글을 작성해보세요!
스크랩 완료