SUMIFS 날짜 기간 조건 합계, AI 수식 만들고 검산

「3월 한 달, 서울, 반품 제외 합계」를 AI 에 말로 시키면 수식은 금방 나옵니다. 어려운 쪽은 그 수식이 맞는지 아는 일입니다. 경계일 행을 일부러 넣은 표로 대조합니다.

자료

날짜·지역·구분·금액 네 열, 60행짜리 가상 판매 표입니다. 반품도 금액은 양수이고 「구분」 열로만 가립니다. 2월 말~4월 초 날짜가 섞여 있고, 서울 행에는 3/1 과 3/31 에 판매·반품이 한 건씩, 앞뒤 날인 2/28 과 4/1 에 판매가 한 건씩 있습니다.

조건은 셀로 뺍니다. F2 시작일 2026-03-01, F3 종료일 2026-03-31, F4 지역 서울, 수식은 F6. 3월 서울 행 12건 중 판매 9건을 손으로 더한 3,810,000 이 맞아야 하는 값입니다.

짧게 시키면

엑셀에서 3월 한 달 서울 지역 반품 제외 매출 합계 수식 만들어 줘.

두 번 모두 열을 「A 날짜, B 지역, C 구분, D 금액」으로 짐작해 같은 모양을 줬습니다.

=SUMIFS(D:D, A:A, ">="&DATE(2026,3,1), A:A, "<"&DATE(2026,4,1), B:B, "서울", C:C, "<>반품")

이 표에서는 3,810,000 이 나와 틀리지 않습니다. 두 번 모두 열 구성이 가정이라고 밝혔지만 수식은 날짜·지역이 박힌 채라 4월을 보려면 고쳐야 합니다. 셀 참조 버전을 덧붙였는데 한 번은 말로만 설명했고, 다른 한 번은 지역이 그대로 박혀 있었습니다. 실제 표의 열 순서가 다르면 안내문을 놓친 사람이 엉뚱한 열을 더하게 됩니다. 그래서 열과 셀을 적어 줍니다.

열과 셀을 적어 시키기

입력과 지시를 태그로 나누는 것은 OpenAI 프롬프트 엔지니어링 안내가 권하는 방식이고, 분석할 열 이름을 적으면 정확해진다는 점은 Microsoft Copilot 수식 안내에도 있습니다.

Write an Excel formula for the sheet described in <sheet>. Use only the cells and columns listed there. <sheet>Data is in A1:D61. Row 1 is the header; rows 2-61 are data.A: 날짜 (real Excel dates, e.g. 2026-03-01)B: 지역 (서울, 부산, 대전, 인천)C: 구분 (판매 or 반품; the amount in D is positive for both)D: 금액 (number, KRW)F2: start date (2026-03-01)F3: end date (2026-03-31)F4: region (서울)The formula goes in F6.</sheet> Task: total of 금액 where 날짜 is from F2 to F3 inclusive, 지역 equals F4, and 구분 is not 반품. Rules:- Use SUMIFS. Compare dates through the cells F2 and F3, not typed-in dates.- Use absolute references ($A$2:$A$61 style); every range must have the same size.- Output in this order, in Korean: (1) the formula in one code block, (2) one line per criterion saying which cell it uses and whether the boundary day is included, (3) two cases where this formula would return 0 or a wrong number.- No other text.

「셀로 비교」는 기간을 바꿀 때 수식을 안 건드리게, 「범위 크기 같게」는 아래 #VALUE! 를 막으려는 규칙입니다. 경계일 포함 여부를 말하게 한 것은 검산에서 볼 곳을 정해 줍니다.

결과

두 번 모두 수식이 같았습니다.

=SUMIFS($D$2:$D$61,$A$2:$A$61,">="&$F$2,$A$2:$A$61,"<="&$F$3,$B$2:$B$61,$F$4,$C$2:$C$61,"<>반품")

조건 설명 4줄에는 시작일·종료일 당일이 모두 포함된다고 적혀 있었고, 「0 또는 틀린 값」 항목에는 텍스트 날짜, 3/31 14:00 처럼 시각이 섞인 날짜, 「반품 」처럼 뒤 공백이 붙은 값이 나왔습니다.

검산

[1] F6 에 붙여 넣어 3,810,000 인지 봅니다. 같은 표와 수식을 계산 엔진(Python formulas 1.3.4)에 넣었을 때 3810000 이었습니다.

[2] 경계일. 종료 조건을 "<"&$F$3 으로 바꾸면 3/31 판매 380,000 이 빠져 3,430,000 이 되어야 합니다(엔진 결과가 그랬습니다). 시작 쪽도 ">"&$F$2 로 바꿔 3/1 판매 250,000 이 빠지는지 봅니다.

[3] 수식과 다른 방법으로 한 번 더. 표에 자동 필터를 걸어 날짜 3/1~3/31, 지역 서울, 구분에서 반품 체크 해제 후 빈 셀에 =SUBTOTAL(9,D2:D61) 을 넣습니다. 9 는 필터로 숨긴 행을 빼고 더합니다. F6 과 같으면 됩니다. 이 필터 단계는 Excel 이 없어 돌리지 못했고, 손으로 더한 값으로 갈음했습니다.

[4] 일부러 깨 봅니다. 같은 표를 엔진에 넣은 결과입니다.

">=F2" 는 셀이 아니라 글자 「F2」 와 비교돼 0 입니다. 합계 범위와 조건 범위의 행 수가 다르면 Excel 도 안 되는 것으로 안내합니다. 시각이 섞인 날짜는 오류도 없이 380,000 만 작아져서 가장 알아채기 어렵습니다. 날짜 셀 서식을 「일반」으로 바꿔 소수점이 보이면 시각이 섞인 것입니다.

빗나가는 곳

• 「반품」이 음수 금액인 표에는 이 수식이 맞지 않습니다. "<>반품" 은 구분이 빈 행도 더합니다.
• 날짜가 텍스트(왼쪽 정렬)인 표는 시험하지 못했습니다. 엔진이 텍스트 날짜를 숫자처럼 읽어 Excel 과 결과가 다를 수 있었습니다. 결과가 0 이면 이것부터 의심합니다.
• 정산·결제에 쓸 금액은 AI 수식 결과만 보고 확정하지 말고 위 검산을 마친 뒤 씁니다.