swoozie
05-09-2006, 09:12 AM
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.
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.