Excel Sumifs based Year and Month to derive Period To Date Amount

[Biz]

Dear All,

I'm trying to create a formula that calculates PTD using Year and Month Numbers.
In Column H, PTD calculation and Colum I Should Be the solution.
Please note I can't create helper columns.

Sumifs Between YearNo and Month No.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Year	Month	Amount			Month	Year	PTD	Should Be	Match
2	2020	1	81,430			2022	7	1,251,978	1,710,248	FALSE
3	2020	2	92,130			2020	8	606,790	606,790	TRUE
4	2020	3	99,310			2020	4	366,650	366,650	TRUE
5	2020	4	93,780			2022	5	962,647	1,623,813	FALSE
6	2020	5	97,110
7	2020	6	54,598
8	2020	7	81,788
9	2020	8	6,644
10	2020	9	572
11	2020	10	29,207
12	2020	11	67,210
13	2020	12	99,848
14	2021	1	44,225
15	2021	2	9,513
16	2021	3	69,688
17	2021	4	25,346
18	2021	5	87,405
19	2021	6	65,616
20	2021	7	894
21	2021	8	28,271
22	2021	9	95,501
23	2021	10	42,431
24	2021	11	25,765
25	2021	12	62,821
26	2022	1	83,334
27	2022	2	51,922
28	2022	3	5,627
29	2022	4	25,320
30	2022	5	96,507
31	2022	6	79,674
32	2022	7	6,761
33	2022	8	1,000
34	2022	9
35	2022	10
36	2022	11
37	2022	12
Sheet1

Cell Formulas
Range	Formula
H2:H5	H2	=SUMIFS($C$2:$C$37,$A$2:$A$37,"<=" &$F2,$B$2:$B$37,"<="&G2)
J2:J5	J2	=I2=H2

Your help would be greatly appreciated.

Kind Regards

Biz

 

[mikerickson]

I think you have your years and months mixed. Col A years are four digits like 2020 in column G years are single digits.
The formula should match column A with F and column B with G, rather than the current reversed formula.

 

[Biz]

Hi Mike,

I have wrong headers but still formula giving incorrect results.

Kind Regards,

Biz

 

[bebo021999]

Your sumproduct does not work for year less than but month greater than, for instant , "2001-12" vs "2002-11"
With year is 4 digits, month is 1-2 digits, try like this:

=SUMPRODUCT(($A$2:$A$37*100+$B$2:$B$37<= F2*100+G2)*$C$2:$C$37)

With year multifly with 100, to convert year-month to 6-digit number: then 200211 >200112

 

[Biz]

Hi Bebo,
Nice solution. Is it possible with sumifs too?

Biz

 

[bebo021999]

Hi Bebo,
Nice solution. Is it possible with sumifs too?

Biz

SUMIF does not work with array like (A1:A100) * 100, but range in sheet like A1:A100
but, if using helper column to change Year - Month into real date (or Year*100+month), SUMIF can work with helper column.

 

[Biz]

SUMIF does not work with array like (A1:A100) * 100, but range in sheet like A1:A100
but, if using helper column to change Year - Month into real date (or Year*100+month), SUMIF can work with helper column.

Thank you for your prompt feedback.