Solved

# =SUMPRODUCT Help

Posted on 2011-03-16
277 Views
Good Afternoon

I need some help on the following SUMPRODUCT:

=(SUMPRODUCT((Centre=C\$4)*(TEXT(Date,"mmmm yyyy")="JANUARY 2011")*(Group=\$A5)*(Value>0)*(Value)))

It returns the value of #VALUE!

As part of trying to solve the problem I have tried:

=(SUMPRODUCT((Centre=C\$4)*(TEXT(Date,"mmmm yyyy")="JANUARY 2011")*(Group=\$A5)*(Value>0)))

Which returns the result of 2 - there are 2 rows which are complient with the above SUMPRODUCT. What I was hoping was that the final (Value) would return the SUM of those two rows: 3.

What am I doing wrong?

Many thanks
0
Question by:Washcare

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 35147677
Try:
=SUMPRODUCT((Centre=C\$4)*(TEXT(Date,"mmmm yyyy")="JANUARY 2011")*(Group=\$A5)*(Value>0),Value)

0

Author Closing Comment

ID: 35147694
Many thanks
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…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…