Solved: Sum a spreadsheet base on 2 criteria

[swoozie]

I am using Window 2000 and Excel 2002 SP3.
I am creating a template where data is paste in one tab and summarized in a second tab.
I was trying to use either Sumproduct or Offset/match but I am not doning something correctly or I am not using the correct formula.
Spreadsheet one(data) looks like:

Spreadsheet name Mar06
A B C D E
1001 Susan S 03/25 2.5
1001 Susan S 03/22 2.5
1001 Susan S 03/21 2.5
1001 George C 03/25 2.5
1002 Kathy C 03/25 2.5

This name is Template
Result I would like is:
S C
1001 7.5 2.5
1002 0 2.5

=SUMPRODUCT((Mar06!$A$1:$Y$4500=$A10)*(Mar06!$D$1:$Y$4500=$C$2)*(Mar06!$X$1 :$X$4500))

and offset I can not seem to remember at all how to use.
=OFFSET(Reference-cell;MATCH(Vertical-searchvalue;search-matrix;0);MATCH(Horizontal-searchvalue;search-matrix;0))


There were no error messages, just a value return of #value
No code yet. I need the formula to work so that I can create the code for remained of app.
Thank you in advance. I did try to look up but did nothave much luck.

[mbarron]

Your formula for the sumproduct is stuctured incorrectly.
The areas you are checking should be only one column wide, not the entire array of data.

The following formula should work. I've based the cell references on your orignial formula, not the data you posted.

=SUMPRODUCT((mar06!$A$1:$A$4500=$A10)*(mar06!$C$1:$C$4500=C$2),(mar06!$X$1: $X$4500))

Note that the the criteria are seperate by * and the "sum column" is seperated by a , (comma).

[lenze]

A Pivot Table produces this result nicely. See attachment

lenze

[swoozie]

Thank you, I knew I was just doing something stupid. That worked perfectly!