• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Sumproduct Separated by Month and Year

S P P

Member
What is wrong with this formula?
 

Attachments

  • SPP Sumproduct Separated by Month and Year.xlsx
    11.5 KB · Views: 8
Code:
=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
Code:
=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
Code:
=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)
 
p45cal

Goodnight!

I couldn't solve any of the formulas.
 

Attachments

  • SPP Sumproduct Separated by Month and Year.xlsx
    11.8 KB · Views: 8
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:
79263

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).
 
Last edited:
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))
 
Back
Top