SumProduct help please

[Phrank]

Good evening,

I'm trying to use SumProduct to do totals based on multiple criteria.
I've tried several different option, but I keep coming up with the
#Value! error. The latest version I've tried is:
=SUMPRODUCT((YEAR(Data!C:C)=$A$1)*(Data!G:G=$A3)*(Data!F:F=$B3)*(Data!D))

Below is the layout of my 'data' sheet.
A B C D E F G
Date Month Year Money In Money out Itemized Who?

Below here is the layout of the totals sheet.

Year
Who Itemized items Money $ In Money $ out
Tom Rent #VALUE!
John Materials #VALUE!
Dee Labor #VALUE!
Dorie Taxes #VALUE!
Renter Insurance #VALUE!
Other Money In #VALUE!

For example, I'm looking to add up the total of, for example, anything
listed for Materials for John in 2010. Thanks for your help.

Frank

 

[Ms-Exl-Learner]

Upto Excel 2003 we should not refer the range in sumproduct to whole
Column like C:C,G:G,F:F,D like that.

Your formula should be like the below:-

=SUMPRODUCT((YEAR(Data!$C$2:$C$100)=$A$1)*(Data!$G$2:$G$100=$A3)*(Data!
$F$2:$F$100=$B3)*(Data!$D$2:$D$100))

At the same time the final sum Column should not have any Text
characters, that is D2100. If it is having any Text character then
also it will result #VALUE! Error.

So that is the reason I have omitted the 1st row in the above formula
since it consist column headers.

I assume that your Year Column of Data sheet is having the Dates and
that is the reason you have used Year function in the sumproduct and
the A1 cell will be having only the Year (i.e.) 2010 like that. In
that case the above formula will work fine.

If suppose both the Year of Data Sheet and A1 cell is Dates then your
sumproduct function should be like the below:-

=SUMPRODUCT((YEAR(Data!$C$2:$C$100)=YEAR($A$1))*(Data!$G$2:$G$100=
$A3)*(Data!$F$2:$F$100=$B3)*(Data!$D$2:$D$100))

Change the C2:C100, G2:G100, F2:F100 and D2100 in the above formula
to your desired range, if required. But keep in mind that all the
cell references should be unique. It should not vary like C2:C60,
G3:G80, F2:F100 and D2:90.

Hope it’s clear to you!.

 

[Phrank]

Good evening. Thanks for your help. I've updated my formula, and
it's no longer returning #VALUE!, but it's now just coming up with 0
for every cell

I did realized that I needed to alter my calculation grid, because I
need to know how much each person spent or received for each category.
So, on the Totals sheet, I've put the categories at the top and the
names along the side, and I've put the year in cell A1. I want to be
able to change the year in cell A1 and have it calculate the amounts
on the DATA sheet for the given year.

2010 Rent Materials Labor Taxes Insurance Money
In
Tom 0
John 0
Dee Dee 0
Aunt Dorie 0
Renter 0
Other 0

Again, here is the layout of my DATA sheet:

A       B       C       D               E               F G
Date    Month   Year    Money In        Money out       Itemized Who?

Thanks for your help!

Frank

 

[Phrank]

Hi again,

I figured it out. Here's the formula that ended up working for me:

=SUMPRODUCT(($A$1=Data!$C$2:$C$1000)*(B$1=Data!$F$2:$F$1000)*($A6=Data!$G$2:$G$1000)*Data!$D$2:$D$1000)

Thank you for your help!

Frank

 

[Ms-Exl-Learner]

Now I can able to imagine the reason on which the formula that I have
provided is getting failed. Your C2:C100 of Data sheet is having only
the Years and not dates. Anyway I am glad that your problem is
getting solved.