Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 524

Sumproduct for Month range with additional criteria problem

I am using the following formula to sum the data by month and where fund type equals "Unrestricted"  I am getting 0 as a value.  Any suggestions?

=SUMPRODUCT(--(MONTH(!\$A\$2:\$A\$20)=MONTH(ReportStartDate)),--(YEAR(\$A\$2:\$A\$20)=YEAR(ReportStartDate)),--\$E\$2:\$E\$20,D2:\$D\$20="Unrestricted")

The formula works until I add in the "Unrestricted" contraint.
Sumproduct-sheet.xlsx
0
Michael Paxton
• 2
1 Solution

Commented:

=SUMPRODUCT((MONTH(\$A\$2:\$A\$20)=MONTH(ReportStartDate))*(YEAR(\$A\$2:\$A\$20)=YEAR(ReportStartDate))*(D2:\$D\$20="Unrestricted"),\$E\$2:\$E\$20)

Thomas
0

Process EngineerAuthor Commented:
Thomas,

This still returns 0 as a solution.

Michael
0

Chief Technology OfficerCommented:
Hi.

I think you want SUMIFS().

Kevin
0

Commented:
It worked for me on your test spreadsheet, after I defined reportstartdate as 6/1/11, see attached.

Since you're using that formula on a different format than your template, it's hard for me to say what the issue is.

Sumifs is of course also an option

=SUMIFS(\$E\$2:\$E\$20,\$A\$2:\$A\$20,">="&DATE(YEAR(ReportStartDate),MONTH(ReportStartDate),1),\$A\$2:\$A\$20,"<"&DATE(YEAR(ReportStartDate),1+MONTH(ReportStartDate),1),D2:\$D\$20,"Unrestricted")

Thomas
Copy-of-Sumproduct-sheet.xlsx
0

Featured Post

• 2
Tackle projects and never again get stuck behind a technical roadblock.