엑셀(Excel) 배열함수로 리스트에서 원하는 구간의 합계 구하기 (구간이 텍스트로 되어 있는 경우)

리스트가 숫자나 날짜 서식으로 되어 있으면 SUMIF 등 여러가지 함수를 통해서 구할 수 있습니다.

하지만 리스트가 문자로 되어있을 수 있습니다.

1월, 2월 ... 혹은 갑, 을, 병 ... 같은 것도 가능합니다.

이런 경우에는 SUMPRODUCT와 배열함수를 사용해야 합니다.

이번에 예시로 사용할 통계는 도로구간별 통행량입니다.

텍스트로 1~12월까지가 리스트에서 필요한 기간의 합계를 구합니다.

▶ 함수내용

이 함수는 배열함수라서 Alt + Shift + Enter로 입력해야 합니다.

{=SUMPRODUCT((B12:B61=B2)*(COLUMN(D11:O11)>=MATCH(C3,$D$11:$P$11,0)+COLUMN(D11)-1)*((COLUMN(D11:O11)<=MATCH(C4,$D$11:$P$11,0)+COLUMN(D11)-1)*D12:O61))}

함수가 상당히 길어집니다. 하나씩 기능을 보겠습니다.

① (B12:B61=B2)

B12:B61는 구간이름들이고 B2에는 구간 이름이 쓰여있습니다.

범위내에서 구간의 이름으로 검색해서 참이면 1, 거짓이면 0이을 반환합니다.

② COLUMN(D11:O11)>=MATCH(C3,$D$11:$P$11,0)+COLUMN(D11)-1

  - Column : 참조영역의 열번호

  - MATCH(C3,$D$11:$P$11,0) : 계산하려는 영역의 시작

  - COLUMN(D11)-1 : 1월의 위치

참조영역(COLUMN(D11:O11))의 열번호를 구하는 영역의 시작(MATCH(C3,$D$11:$P$11,0))이 보다 커야(>=) 합니다.

다만 시작하는 칸이 맨처음이 아니니 1월의 위치(COLUMN(D11)-1)를 찾아서 값을 보정해 줍니다.

③ COLUMN(D11:O11)<=MATCH(C4,$D$11:$P$11,0)+COLUMN(D11)-1

  - Column : 참조영역의 열번호

  - MATCH(C4,$D$11:$P$11,0) : 계산하려는 영역의 끝

  - COLUMN(D11)-1 : 1월의 위치

참조영역(COLUMN(D11:O11))의 열번호를 구하는 영역의 시작(MATCH(C4,$D$11:$P$11,0))이 보다 작아야(<=) 합니다.

다만 시작하는 칸이 맨처음이 아니니 1월의 위치(COLUMN(D11)-1)를 찾아서 값을 보정해 줍니다.

④ SUMPRODUCT, D12:O61 

위의 ①, ②,③ 조건식을 통해 범위내에 숫자가 조건을 만족하면 1이 되고 조건을 불만족하면 0이 됩니다.

곱셈을 하니 1이 되면 숫자가 남고 0을 곱하면 0이 되어 없어지게 합니다.

그런 후 더하면 우리가 원하는 숫자만 더 할 수 있습니다.

파일을 추가로 첨부합니다.