Solved

# Sumproduct for Month range with additional criteria problem

Posted on 2012-09-07
475 Views
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
Question by:paxtonm

LVL 39

Expert Comment

=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

Author Comment

Thomas,

This still returns 0 as a solution.

Michael
0

LVL 59

Expert Comment

Hi.

I think you want SUMIFS().

Kevin
0

LVL 39

Accepted Solution

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

### Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…