엑셀 SUMIFS로 날짜와 상태별 수량 합계 구하는 방법

결론부터

날짜와 상태가 함께 맞는 수량은 SUMIFS로 모을 수 있다. 시간까지 저장된 날짜는 기준일 이상, 다음 날 미만으로 범위를 잡아야 당일 기록이 빠지지 않는다. 사본에서 날짜와 수량의 형식을 확인하고, 합계와 일치 행 개수를 함께 비교한 뒤 적용한다.

필터를 바꿀 때마다 합계를 다시 적는 문제

예를 들어 접수 기록에서 특정 날짜의 포장 완료 수량을 모은다고 가정한다. 날짜 필터를 바꾸고 상태를 선택한 뒤 보이는 수량을 다른 칸에 옮기는 작업이 반복된다. 조건을 바꾸는 과정에서 이전 필터가 남거나 다른 날짜의 합계를 붙여 넣을 수 있다.

원본 시트의 A열은 접수일시, B열은 분류, C열은 상태, D열은 수량으로 정한다. 별도 집계 시트의 F2에는 기준일, G2에는 분류, H2에는 상태를 입력한다. 기준일은 시간이 없는 실제 날짜 값이어야 한다.

아래에서 사용하는 행 번호와 수량은 모두 예시 숫자다. 설명용 범위를 실제 기록의 크기에 맞게 바꾸되, 모든 조건 범위와 합계 범위의 행 위치를 동일하게 유지한다.

먼저 집계 기준을 문장으로 적는다. 여기서는 접수일시가 기준일에 속하고, 분류와 상태가 각각 지정한 값인 행의 수량을 더한다. 출고일이나 수정일을 기준으로 삼는 다른 보고서와 혼동하지 않도록 한다.

집계 결과를 표시할 칸에 다음 수식을 입력한다.

=SUMIFS(원본!$D$2:$D$1000,원본!$A$2:$A$1000,">="&$F$2,원본!$A$2:$A$1000,"<"&($F$2+1),원본!$B$2:$B$1000,$G$2,원본!$C$2:$C$1000,$H$2)

날짜 조건을 두 번 쓰는 이유는 접수일시에 시간이 포함될 수 있기 때문이다. 기준일과 정확히 같은 값만 찾으면 그날 자정 이후에 입력된 기록이 빠질 수 있다. 시작은 포함하고 다음 날 시작은 제외하면 당일 시간대를 한 구간으로 묶는다.

F2에 시간이 들어 있으면 구간도 그 시간부터 시작한다. 화면에 날짜만 보이더라도 수식 입력줄에서 실제 값을 확인한다. 표시 형식을 바꾸는 일과 저장된 시간 값을 없애는 일은 다르다.

Microsoft 지원의 SUMIFS 설명은 여러 조건을 모두 만족하는 행의 값을 합산하는 구조를 안내한다. 이 수식도 날짜, 분류, 상태 조건을 동시에 적용한다.

검증용 칸에는 같은 조건의 행 개수를 구한다.

=COUNTIFS(원본!$A$2:$A$1000,">="&$F$2,원본!$A$2:$A$1000,"<"&($F$2+1),원본!$B$2:$B$1000,$G$2,원본!$C$2:$C$1000,$H$2)

합계가 0일 때 일치하는 행 자체가 없는지, 행은 있지만 수량 합계가 0인지 구분할 수 있다. 두 수식은 조건을 수정할 때 함께 바꾼다.

주의

원본을 복사해 시험하고, 기준일과 조건 입력 칸을 비워 둔 상태의 결과를 업무 수치로 사용하지 않는다.

예를 들어 같은 날 같은 분류의 완료 기록에 수량 4와 6이 있고, 대기 기록에 8이 있다고 가정한다. 모두 예시 숫자다. 완료를 조건으로 선택하면 합계는 10, 일치 행 개수는 2가 되어야 한다.

여기에 다음 날 기록을 넣어도 결과가 달라지지 않아야 한다. 기준일의 늦은 시각에 해당하는 기록은 포함되어야 한다. 날짜 경계에 시험 행을 넣으면 조건이 의도대로 작동하는지 확인하기 쉽다.

행 개수가 예상과 같은데 합계가 작다면 수량이 텍스트로 저장되었는지 확인한다. 숫자처럼 보이는 문자열은 합산에서 빠질 수 있다. 단위를 셀 안에 붙여 적은 수량도 정리 대상이다.

이 방식은 조건을 바꿀 때마다 결과를 다시 옮기는 단계를 줄인다. 다만 원본을 정리하는 시간까지 없어지는 것은 아니다. 실제 시간 절감은 조건 변경, 오류 확인, 결과 기록을 포함해 따로 측정해야 한다.

접수일시가 텍스트라면 날짜 구간 비교가 기대대로 작동하지 않을 수 있다. 다른 시스템에서 붙여 넣은 값은 일부 행만 텍스트인 경우도 있으므로 정상 행 하나만 보고 전체를 판단하지 않는다.

상태 표기도 통일한다. 완료와 완료 뒤에 공백이 붙은 값은 같은 조건으로 처리되지 않을 수 있다. 상태 입력을 선택 목록으로 제한하면 새로 생기는 표기 차이를 줄이는 데 도움이 된다.

이 글의 분류와 상태는 별표나 물음표를 포함하지 않는 단순한 값으로 가정한다. 조건 문자열에 그런 기호가 실제 문자로 쓰이면 와일드카드 해석을 고려해 별도 처리가 필요하다.

합계 범위를 전체 열로 넓히면 관리가 쉬워 보이지만, 많은 집계 수식이 동시에 계산될 때 부담이 커질 수 있다. 실제 데이터 범위를 관리하거나 표의 열 참조를 사용하는 방식을 검토한다.

최종 적용 전에는 기준일의 저장 값, 상태 표기, 수량 형식, 마지막 데이터 행을 확인한다. 합계가 맞아 보여도 일치 행 개수가 다르면 중복이나 조건 누락부터 조사한다.