엑셀 구분기호 숫자 합계 공식
2010지원 버전 자세히 보기
셀 안의 구분기호 숫자를 더하는 합계 공식입니다.
인수 설명
01M365·Excel 2024 공식
=SUM ( IFERROR ( --TEXTSPLIT ( 셀, "구분기호" ), 0 ) )
=SUM ( IFERROR ( --TEXTSPLIT ( 셀, {"기호1","기호2"} ), 0 ) )
인수 설명 자세히 보기(인수를 클릭하면 해당 영역만 강조됩니다. 여러 개를 함께 켜고 끌 수 있습니다.)
02Excel 2010~2021 배열수식
{=SUM ( IFERROR ( --TRIM ( MID ( SUBSTITUTE ( "구분기호"&셀, "구분기호", REPT ( " ", LEN ( 셀 )+1 ) ), ROW ( INDIRECT ( "A1:A"& ( ( LEN ( 셀 )-LEN ( SUBSTITUTE ( 셀, "구분기호", "" ) ) ) / LEN ( "구분기호" )+1 ) ) ) * LEN ( 셀 )+1, LEN ( 셀 )+1 ) ), 0 ) )}인수 설명 자세히 보기
동작 원리
01M365·Excel 2024 공식
TEXTSPLIT 함수는 셀의 값을 구분기호로 나누고, 이중 부호와 IFERROR 함수가 숫자만 남긴 뒤 SUM 함수가 합계를 계산합니다.
M365·Excel 2024 공식의 예제 시트에서 D2 셀이 300을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
TEXTSPLIT 함수가 값을 나눕니다
TEXTSPLIT 함수는 B2 셀의 문자열을 쉼표 기준으로 나눠 네 개의 값을 반환합니다.
=TEXTSPLIT ( B2, "," )
= {"120"," 80"," 가"," 100"}
쉼표로 나눈 네 값입니다. 이중 부호가 숫자로 변환합니다
이중 부호(--)는 분리된 텍스트 숫자를 숫자 데이터로 변환합니다. 숫자로 변환할 수 없는 ‘가’는 #VALUE! 오류를 반환합니다.
=--TEXTSPLIT ( B2, "," )
= {120,80,#VALUE!,100}
숫자 세 개는 변환되고 ‘가’에서 오류가 발생합니다. IFERROR 함수가 오류를 0으로 바꿉니다
IFERROR 함수는 이중 부호가 반환한 #VALUE! 오류를 0으로 바꿉니다.
=IFERROR ( --TEXTSPLIT ( B2, "," ), 0 )
= {120,80,0,100}
오류가 0으로 바뀐 숫자 배열입니다. SUM 함수가 합계를 계산합니다
SUM 함수는 120+80+0+100을 더해 D2 셀의 연간 합계를 계산합니다.
=SUM ( IFERROR ( --TEXTSPLIT ( B2, "," ), 0 ) )
= 300
120+80+0+100의 합계입니다. 02Excel 2010~2021 배열수식
SUBSTITUTE 함수와 REPT 함수가 구분기호를 긴 공백으로 바꾸고, ROW 함수와 INDIRECT 함수가 만든 위치에서 MID 함수가 값을 꺼내면 이중 부호와 IFERROR 함수가 숫자만 남긴 뒤 SUM 함수가 합계를 계산합니다.
구버전 호환 배열수식의 예제 시트에서 D2 셀이 300을 반환하는 계산 과정입니다. 단계마다 강조된 부분이 어떻게 계산되는지 살펴보세요.
ROW 함수가 추출 위치를 만듭니다
LEN 함수와 SUBSTITUTE 함수로 B2 셀의 쉼표 세 개를 세면 값은 네 개입니다. INDIRECT 함수가 A1:A4 참조를 만들고 ROW 함수가 각 순번에 B2 셀의 길이 15를 적용해 추출 시작 위치를 계산합니다.
{=ROW ( INDIRECT ( "A1:A"& ( ( LEN ( B2 )-LEN ( SUBSTITUTE ( B2, ",", "" ) ) ) / LEN ( "," )+1 ) ) ) * LEN ( B2 )+1}
= {16;31;46;61}
B2 셀에서 각 값을 꺼낼 시작 위치입니다. MID 함수가 각 값을 추출합니다
SUBSTITUTE 함수와 REPT 함수가 쉼표를 16칸의 공백으로 바꿉니다. MID 함수는 계산된 네 위치에서 같은 길이로 값을 꺼내고 TRIM 함수가 양쪽 공백을 제거합니다.
{=TRIM ( MID ( SUBSTITUTE ( ","&B2, ",", REPT ( " ", LEN ( B2 )+1 ) ), {16;31;46;61}, LEN ( B2 )+1 ) )}
= {"120";"80";"가";"100"}
공백을 제거하고 남은 네 값입니다. 이중 부호와 IFERROR 함수가 숫자만 남깁니다
이중 부호는 텍스트 숫자를 숫자 데이터로 바꾸고, IFERROR 함수는 숫자가 아닌 ‘가’에서 생긴 오류를 0으로 바꿉니다.
{=IFERROR ( --TRIM ( MID ( SUBSTITUTE ( ","&B2, ",", REPT ( " ", LEN ( B2 )+1 ) ), {16;31;46;61}, LEN ( B2 )+1 ) ), 0 )}
= {120;80;0;100}
숫자로 바꿀 수 있는 값만 남긴 배열입니다. SUM 함수가 합계를 계산합니다
SUM 함수는 120+80+0+100을 더해 D2 셀의 연간 합계를 계산합니다. 동적 배열을 지원하지 않는 버전에서는 수식을 Ctrl+Shift+Enter로 확정합니다.
{=SUM ( IFERROR ( --TRIM ( MID ( SUBSTITUTE ( ","&B2, ",", REPT ( " ", LEN ( B2 )+1 ) ), ROW ( INDIRECT ( "A1:A"& ( ( LEN ( B2 )-LEN ( SUBSTITUTE ( B2, ",", "" ) ) ) / LEN ( "," )+1 ) ) ) * LEN ( B2 )+1, LEN ( B2 )+1 ) ), 0 ) )}
= 300
120+80+0+100의 합계입니다. 
위의 예제에서 셀안에 있는 데이터의 합계를 구하는 것까진 이해됐는데 ,
한 셀 안의 숫자들을 합한 뒤 여기에
다른 셀의 데이터들도 합해서 총 합을 구하려면 수식을 어떻게 써야할지 여쭐 수 있을까요?
다른 셀의 합계를 더하려면 수식을 다음과 같이 수정해서 사용해보세요.
=SUM(IFERROR(VALUE(TEXTSPLIT(TEXTJOIN("구분기호",,범위,범위,범위,...),"구분기호")),0))
TEXTJOIN 함수로 여러 범위의 문장을 하나로 합친 후, 합계를 구하는 원리입니다.문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.