# Sumproduct Separated by Month and Year

#### S P P

##### Member
What is wrong with this formula?

#### Attachments

• 11.5 KB Views: 8

#### p45cal

##### Well-Known Member
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)``

• Hany ali

#### S P P

##### Member
p45cal

Goodnight!

I couldn't solve any of the formulas.

#### Attachments

• 11.8 KB Views: 8

#### herofox

##### Active Member
thanks alot
p45cal
show Please every Thing is Ok

#### Attachments

• 13.4 KB Views: 8
• Hany ali

#### p45cal

##### Well-Known Member
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).

Last edited:

#### S P P

##### Member
p45cal

Good Morning!

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

#### S P P

##### Member
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

##### Well-Known Member
More conventionally:
Code:
``=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 and herofox

#### S P P

##### Member
p45cal

With this formula it worked

Thanks