[RESOLVED] 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.

[DKenny]

Swoozie
There are 2 good apporaches to this problem.

1/ Use a pivot table.

2/ Use an Array Formula.
Array formula's are a really powerful but underused tool within Excel. They can be used to aggregate data based on multiple conditions, like your case.
Here is a sample file that includes an Array Formula solution to your problem.

Note: When entering an Array formula you must press Ctrl&Shft&Enter instead of just Enter, this will add the { brackets in the formula bar - which is how Excel denotes Array Formulas. Adding the { brackets manually will not work.

[swoozie]

I do not like pivot tables

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


I had a simple syntax error. I was searching the entire range of cells and not just the column.