Sumproduct Separated by Month and Year

[S P P]

What is wrong with this formula?

 

[p45cal]

=SUMPRODUCT(--($D$15:$D$20000=$D$2),--(TEXT($B$15:$B$20000,"m")=VALUETOTEXT(E2)),--(TEXT($B$15:$B$20000,"yyyy")=VALUETOTEXT($C$2)),$C$15:$C$20000)

or

=SUMPRODUCT(--($D$15:$D$20000=$D$2),--(TEXT($B$15:$B$20000,"m")=E2&""),--(TEXT($B$15:$B$20000,"yyyy")="" & $C$2),$C$15:$C$20000)

or

=SUMPRODUCT(($D$15:$D$20000=$D$2)*(TEXT($B$15:$B$20000,"m")=E2&"")*(TEXT($B$15:$B$20000,"yyyy")=$C$2&""),$C$15:$C$20000)

 

[S P P]

p45cal

Goodnight!

I couldn't solve any of the formulas.

 

[herofox]

thanks alot
p45cal
show Please every Thing is Ok

 

[p45cal]

I couldn't solve any of the formulas.

Except for an added character or two in the formulae in column H, it's fine here:


I opened your file in Excel 2003 when all the values showed 0, but re-entering the formulae (go into edit mode and straight away press Enter) brought up the correct answers. VALUETOTEXT isn't recognised by Excel 2003.

show Please every Thing is Ok

@herofox , you're getting slightly different results for May and Jan because of something funny going on with the dates in cells B15 (and B16, but it doesn't affect the result because there's no D in D16).

 

[S P P]

p45cal

Good Morning!

I couldn't solve any of the formulas I'm using excel 2010

 

[S P P]

With this formula I can add up the whole month separately. How to add column D in this formula

=SUMPRODUCT((MONTH($B$15:$B$20000)=E2)*($C$15:$C$20000))

 

[p45cal]

More conventionally:

=SUMPRODUCT($C$15:$C$36,($D$15:$D$36=$D$2)*(MONTH($B$15:$B$36)=$E2)*(YEAR($B$15:$B$36)=$C$2))

 

[S P P]

p45cal

With this formula it worked

Thanks