Sumproduct calculation

[Av8tordude]

I have a table i would like to sum the total for each month of the year, however, I'm not sure why is not calculating the sum. Can someone assist.
The array formula I'm using...

=SUMPRODUCT(MONTH(D16:D45)=T3,YEAR(D16:D45)=U2,P16:P45)

Thank you

 

[Av8tordude]

It seems the "MONTH(D16:D45)=T3 is returning false. T3=January however, I have 6 items listed for the month of January.

 

[Peter_SSs]

If T3 is Text "January", then your structure would need to be
=SUMPRODUCT(--(MONTH(D16:D45)=MONTH("1-"&T3)),--(YEAR(D16:D45)=U2),P16:P45)

but you could also use
=SUMPRODUCT(--(TEXT(D16:D45,"mmmmyyyy")=T3&U2),P16:P45)

and if you have the SUMIFS function
=SUMIFS(P16:P45,D16:D45,">=1"&T3&U2,D16:D45,"<"&EDATE("1-"&T3&U2,1))

 

[Av8tordude]

Thank you Peter. Is it possible to make this formula dynamic. I will be adding more data to the table?

=SUMPRODUCT(--(TEXT(D16:D45,"mmmmyyyy")=T3&U2),P16:P45)

 

[Peter_SSs]

I will be adding more data to the table?

Is it a formal table (created via Insert -> Table) or just an ordinary range of cells with headings?

 

[Av8tordude]

it is an ordinary range . Range S1:V14

[TABLE="width: 75"]
<tbody>[TR]
[TD][TABLE="width: 300"]
<tbody>[TR]
[TD]Month[/TD]
[TD]2018[/TD]
[TD]2019[/TD]
[TD]2020[/TD]
[/TR]
[TR]
[TD]January[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]February[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]March[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]April[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]May[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]June[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]July[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]August[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]September[/TD]
[TD] $ 1,181.26[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]October[/TD]
[TD] $ 16,016.55[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]November[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]December[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
[TR]
[TD]YTD Total[/TD]
[TD] $ 17,197.81[/TD]
[TD] $ -[/TD]
[TD] $ -[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peter_SSs]

Is it possible to make this formula dynamic. I will be adding more data to the table?

=SUMPRODUCT(--(TEXT(D16:D45,"mmmmyyyy")=T3&U2),P16:P45)

it is an ordinary range . Range S1:V14

So, couldn't you just change the 45's in the formula to 100 or 1000 or whatever would be required to ensure that it will be big enough to always cover your table?

 

[Av8tordude]

So, couldn't you just change the 45's in the formula to 100 or 1000 or whatever would be required to ensure that it will be big enough to always cover your table?

Yes...you have a point. Didn't consider the future file size. Thank you for help and great expertise.

 

[Av8tordude]

disregard...found a solution. Thanks