Sumproduct to sum date range

[erniepoe]

Hi all,

Have a formula that is giving me a #spill error, likely because the formula is in row 87. Row 2 is months from 2023-2029, and i want to sum all the values in row 87 if the constraints hold, i.e. sum all the values for that year up to the current month. So sum jan-may if in june, jan-july if in august, etc.

Anyone know how i can rewrite this formula to be in row 87 without error? Thank you so much.

Cheers,
Ernie

=SUMPRODUCT(--(YEAR($S$2:$CX$2)=YEAR(V$2))*($S$2:$CX$2<V$2)*($S$87:$CX$87))

 

[DanteAmor]

Try:

=SUMPRODUCT(($S$2:$CX$2<DATE(YEAR(TODAY()),MONTH(TODAY()),1))*($S$87:$CX$87))

 

[Fluff]

Can you post some sample data.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Alex Blakenburg]

As was the case with your previous thread that Fluff also responded to, the formula works fine in principle and does not cause a #SPILL error when I try it.
I tried some different combinations and can not get it to generate a spill error.
If you want everything in the current year up the current month (ie YTD) using V2 doesn't seem to make sense. We would expect to see either Today() in the formula or a cell that contains your reporting date.
(Dante's formula does not restrict the selection to the current year, so if you want current YTD it won't work for you but may still be helpful in seeing if it overcomes your Spill scenario)

If you respond to Fluff's request for an XL2BB we can see if we can figure out what is going on.

20231031 Sum Date Range SumProduct erniepoe.xlsm
	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
2			Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23	Oct-23	Nov-23	Dec-23	Jan-24
86	erniepoe	erniepoe using Today()
87	30	90	10	10	10	10	10	10	10	10	10	10	10	10	10
Sheet1

Cell Formulas
Range	Formula
Q87	Q87	=SUMPRODUCT((YEAR($S$2:$CX$2)=YEAR(V$2))*($S$2:$CX$2<V$2)*($S$87:$CX$87))
R87	R87	=SUMPRODUCT((YEAR($S$2:$CX$2)=YEAR(TODAY()))*($S$2:$CX$2<EOMONTH(TODAY(),-1))*($S$87:$CX$87))

 

[Fluff]

The only way I can get a #spill error is if that error is in either of the two ranges used in the formula