Sum Product to combine year and month not separately

[pedexe90]

Hello everyone,

So I have another issue. I'm trying to use a sumproduct function to return sums based on year and month. But for some reason the function does not work. What I need the function to do is to combine both conditions for year and month and only return if both are met. For instance (as you can see per attached) the figures should not change from Oct 18 onward as I do not have any items in the column for post that period.

https://1drv.ms/x/s!AgQdYsSdg735gQMgDjJPDd0MYlWd

Many thanks

 

[Special-K99]

Not sure this is right

in C3
=SUMPRODUCT((TEXT($AB$3:$AB$2000,"yymm")=TEXT(C$2,"yymm"))*($AA$3:$AA$2000))
and copy across the columns

 

[Dave Patton]

N.B. I did not view the info you referenced

You can consider the following approach.

Excel 2010
	C
2	1-Oct-18
3	27
4
4a

Cell Formulas
Range	Formula
C3	=SUMPRODUCT(--($AB$3:$AB$2000-DAY($AB$3:$AB$2000)+1=C2),($AA$3:$AA$2000))

 

[Peter_SSs]

If you have the SUMIFS function in your Excel version it is more efficient than SUMPRODUCT. It would be something like

=SUMIFS($AA$3:$AA$2000,$AB$3:$AB$2000,"<="&C$2,$AB$3:$AB$2000,">"&C$2-DAY(C$2))