Sumifs or Pivot 도움 부탁드립니다.

안녕하세요,

기준 스프레드시트에 있는 자료를 기반으로 Cach flow 자료를 만들려고 하는데,

막히는 부분이 있어서 도움 부탁드리고자 글을 남깁니다.

처음에는 pivot으로 작성해보려 했는데, 밸류 열에 2개의 조건을 넣는 부분에서 막혀서 sumifs 함수를 사용하는 쪽으로 작성해보았습니다.

아래는 제가 어떤 식으로 자료를 만들었는지에 대한 설명입니다.

살펴보시고 어디가 잘못됐는지, 혹은 다른 더 좋은 방법이 있다면 조언 부탁드립니다.

기준이 되는 [All] 탭입니다.

여기에 있는 자료를 기반으로 아래와 같이 [test] 탭에 고객별 / 월별 예상 입금액과 실제 입금액을 정리하는 자료를 만들려고 합니다.

(1) [all] 탭의 자료는 인보이스에 따라 입력되어 있기 때문에 customer ID 가 중복되어 있습니다.

따라서 [test] 탭에 sort / unique 함수를 이용하여 자료의 기준이 되도록 cusomer ID를 가져왔습니다.

(2) 그 다음, 날짜를 2024년 1월 이전과 2024년 월별로 분류하였고,

[all] 탭에서 'total amount'와 'paid amount' 를 각각 [test] 탭에서 'expected' 와 'actual' 로 가지고 왔습니다.

이때 'expected'의 기준일은 H열 'due date'가 되고, 'actual'의 기준일은 I열 'date paid'가 됩니다.

즉, due date 이 1월인 인보이스는 1월 expected에 합산되고,

실제 입금일이 1월인 경우 1월 acutal에 합산됩니다.

가끔 총액이 아닌, 일부 금액만 지불하는 경우가 있는데 이때는 date paid 가 공란이므로,

[test] 탭 B열에 'partial PMT' 를 따로 두었습니다.

(3) 아래와 같이 sumifs 함수를 사용하여 [all] 탭에서 고객ID에 따라 고객별/월별로 expected와 actual의 합을 가져오도록 하였습니다.

(4) 문제는, 이렇게 했을 때 [all]탭의 총합과 [test]의 총합이 같아야 하는데, 함수가 잘못되었는지 둘이 일치하지 않습니다.

제가 자료를 만드는 과정에서 어느 부분이 잘못된 것인지 살펴봐 주시기를 부탁드립니다.

혹시 필요하실 수 있을 것 같아 엑셀파일 첨부하였습니다.

감사합니다.