SUMIF across Columns

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Back
Top