리스트가 숫자나 날짜 서식으로 되어 있으면 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이 되어 없어지게 합니다.
그런 후 더하면 우리가 원하는 숫자만 더 할 수 있습니다.
파일을 추가로 첨부합니다.