I used Max's formula and adjusted if for my data. The problem is because I
don't understand the formula I don't know what I'm doing wrong. The formula
Max sent was for only a few columns of data and my data has (E through FZ -
how many that is I don't have time to figure out). The table I want to sum
from is called "Data" and contains division initials in the first row (SO,
NW, NO, etc). I want to look at the division listed in F4 of a tab called
"Graph", evaluate the division code there and sum each var going down the
page based on which division it belongs to.

Max's formula -
SUM(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,,,MATCH(B$1,Sheet1!$1:$1,0)-1))

My formula -
=SUM(OFFSET(DATA!$E$2:$FZ$2,MATCH(Graph!$F$4,DATA! $E:$E,0)-1,,,MATCH(Graph!$F$4,DATA!$2:$2,0)-1))

My formula does summarize but only four columns of data and one of those
columns is not NW. Anybody know what I'm doing wrong or a better formula to
use. (I tried Julid D's which is also shown in the post but I couldn't
figure out how to adapt that one to my data)

DATA sheet
NW NW NW NS SO WT NW
var 40 40 20 10 10 3 40

Graph sheet

Division NW
var 140 (s/b based on above example)

Try this:

Assumes headings in Data sheet are in row 1, starting column E and "var" is
in column A

On Graph: result for NW (from your example) is in B2

Formula in B2:

A2 contains "var", B1 contains "NW" or whatever is selected (B1 could be
changed to $F$4)

=SUMPRODUCT(--(Data!$A$2:$A$20=$A2)*(Data!$E$1:$FZ$1=$B$1)*(Data !$E$2:$FZ$20))

Copy down for list of "var"s

Change 20 to reflect rows in Data

HTH

"JICDB" wrote:

I used Max's formula and adjusted if for my data. The problem is because I
don't understand the formula I don't know what I'm doing wrong. The formula
Max sent was for only a few columns of data and my data has (E through FZ -
how many that is I don't have time to figure out). The table I want to sum
from is called "Data" and contains division initials in the first row (SO,
NW, NO, etc). I want to look at the division listed in F4 of a tab called
"Graph", evaluate the division code there and sum each var going down the
page based on which division it belongs to.

Max's formula -
SUM(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,,,MATCH(B$1,Sheet1!$1:$1,0)-1))

My formula -
=SUM(OFFSET(DATA!$E$2:$FZ$2,MATCH(Graph!$F$4,DATA! $E:$E,0)-1,,,MATCH(Graph!$F$4,DATA!$2:$2,0)-1))

My formula does summarize but only four columns of data and one of those
columns is not NW. Anybody know what I'm doing wrong or a better formula to
use. (I tried Julid D's which is also shown in the post but I couldn't
figure out how to adapt that one to my data)

DATA sheet
NW NW NW NS SO WT NW
var 40 40 20 10 10 3 40

Graph sheet

Division NW
var 140 (s/b based on above example)

I'm getting NA and I'm not sure why so please allow me to summarize what you
suggested to be sure I understand. I see three different pieces:

=SUMPRODUCT(--(Data!$A$2:$A$20=$A2)*(Data!$E$1:$FZ$1=$B$1)*(Data !$E$2:$FZ$20))

Part one - the range is on the data sheet and is made up of all of the
variable names which it matched to the one on the graph sheet (in this case
$A2) multiply by

Part two - the range is the heading on the data sheet that contain all of
the division names which match to the one on the graph sheet (in this case
$B$1) multiply by

Part three - this is the range where the data to be added together is based
on the two previous criteria

Am I understanding this correctly, or missing something.

Patti

"Toppers" wrote:

Try this:

Assumes headings in Data sheet are in row 1, starting column E and "var" is
in column A

On Graph: result for NW (from your example) is in B2

Formula in B2:

A2 contains "var", B1 contains "NW" or whatever is selected (B1 could be
changed to $F$4)

=SUMPRODUCT(--(Data!$A$2:$A$20=$A2)*(Data!$E$1:$FZ$1=$B$1)*(Data !$E$2:$FZ$20))

Copy down for list of "var"s

Change 20 to reflect rows in Data

HTH

"JICDB" wrote:

I used Max's formula and adjusted if for my data. The problem is because I
don't understand the formula I don't know what I'm doing wrong. The formula
Max sent was for only a few columns of data and my data has (E through FZ -
how many that is I don't have time to figure out). The table I want to sum
from is called "Data" and contains division initials in the first row (SO,
NW, NO, etc). I want to look at the division listed in F4 of a tab called
"Graph", evaluate the division code there and sum each var going down the
page based on which division it belongs to.

Max's formula -
SUM(OFFSET(Sheet1!$B$1,MATCH($A2,Sheet1!$A:$A,0)-1,,,MATCH(B$1,Sheet1!$1:$1,0)-1))

My formula -
=SUM(OFFSET(DATA!$E$2:$FZ$2,MATCH(Graph!$F$4,DATA! $E:$E,0)-1,,,MATCH(Graph!$F$4,DATA!$2:$2,0)-1))

My formula does summarize but only four columns of data and one of those
columns is not NW. Anybody know what I'm doing wrong or a better formula to
use. (I tried Julid D's which is also shown in the post but I couldn't
figure out how to adapt that one to my data)

DATA sheet
NW NW NW NS SO WT NW
var 40 40 20 10 10 3 40

Graph sheet

Division NW
var 140 (s/b based on above example)