Excel_텍스트와 숫자 합쳐진 셀에서 숫자만 합계내기

안녕하세요,

 

엑셀에서 표를 입력하는 작업을 하다 보면 다음과 같이 숫자와 텍스트 형식이 같이 섞여있는 셀들에 대해서 합계를 내야 될 때가 있습니다.

 

그런데 이 경우에는 원래 우리가 익히 잘 알고 있는 단순한 SUM함수로는 아래에서 보시는 것처럼 합계가 구해지지 않습니다.

 

반면에 C열의 숫자만 입력된 셀에서는 SUM함수로 손쉽게 합계를 구할 수 있죠.

 

C열의 예에서 보신 것처럼, B열의 합계가 구해지지 않는 이유는 B열의 셀들에 “원”이라는 한글 텍스트가 추가되어 있기 때문인데요.

이 마지막의 텍스트 “원”을 제외하고 나머지 데이터만 숫자로 합계 구할 수 있는 방법을 알아보도록 하겠습니다.

 

SUMPRODUCT라는 함수와 LEFT, LENGTH함수를 쓰는 다음과 같은 방법입니다.

 

=SUMPRODUCT(--LEFT(영역,LEN(영역)-1))

 

제가 위에서 예로 든 경우에서는 다음과 같이 수식이 표현될 수 있습니다.

=SUMPRODUCT(--LEFT(B3:B9,LEN(B3:B9)-1))

 

• 제일 마지막의 “원”이라는 텍스트 하나만을 제외하고 셀의 데이터를 왼쪽에서부터 선택하고,
• --는 SUMPRODUCT 함수에서 셀의 합계를 구하고자 할 때 텍스트형식 데이터를 숫자형식으로 변환시켜줍니다. -1을 두 번 곱한 결과, 그러니까, (-1)*(-1)이 곱해져서 결과적으로 영역의 합계 내용을 그대로 반환하는 식이 됩니다. SUM(B3:B9)의 결과와 같은 내용이죠

아니면 다음과 같이 써줄 수도 있습니다.

=SUMPRODUCT(1*LEFT(B3:B9,LEN(B3:B9)-1))

 

SUMPRODUCT는 텍스트가 포함된 셀에서 합계를 구하는 함수이기에 그대로 사용하면 수의 합계가 안 나오고 위와 같은 조건들이 포함되어야(“--” 혹은 “1*” 추가) 원래 우리가 원하는 수의 합계가 구해지게 됩니다.

 

그러면 위 함수식을 엑셀 파일에 적용해볼게요.

 

위와 같이 수식을 적용해주면, 우리가 원래 의도했던 바대로, B열에서 가격 숫자데이터만을 모두 더한 합계가 마지막 셀에 계산되어 나타나게 됩니다. 원래 숫자만 입력되어 있던 C열의 합계 금액과도 맞아떨어지네요. :)

 

여기서 한단계 더 나아가서, 원래 형식대로 합계 숫자 뒤에 “원”을 붙여주면 더 완벽해질 수 있을 것 같은데요.

합계 값이 계산된 셀을 선택하고 Ctrl+1을 눌러주면 셀서식 대화상자가 아래와 같이 보여집니다.

 

왼쪽의 사용자 지정을 클릭한 후 G/표준을 선택합니다.
제일 위의 형식 필드에 다음과 같이 입력해줍니다.

 

그러면 합계 결과까지 아래와 같이 ~원으로 표시되는 것을 보실 수 있을 겁니다.

 

간혹 이런 단위가 텍스트로 입력이 되어있는 경우 그 합계를 구할 때 곤란했던 경험이 있어서, 위와 같은 엑셀 함수 수식이 다른 분들께도 유용하게 사용될 수 있을 것 같아 이번 포스팅으로 정리해봤습니다.

그럼 다음번에 또 유용하고 좋은 정보로 다시 돌아올게요.

 

감사합니다.