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
  • Last Modified:

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
Asked:
Michael Paxton
  • 2
1 Solution
 
nutschCommented:
Try * instead

=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
 
Michael PaxtonProcess EngineerAuthor Commented:
Thomas,

This still returns 0 as a solution.

Michael
0
 
Kevin CrossChief Technology OfficerCommented:
Hi.

I think you want SUMIFS().

Kevin
0
 
nutschCommented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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