Named ranges - column/row question

C

Carole O

Excel 2003

I have a total sheet that gets data from three different shift sheets (all
in the same workbook) for a monthly report. I have named ranges on each
shift sheet for each month's production data (S1M1, S1M2, S1M3, S2M1...)
where S1M1 =A2:AC147, S1M2 = A148:AC329, etc. The old formula
was:=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147). I'm stumped as
to what the new formula would be - how would I direct Excel to column L of
S1M1 to search for "BB", then add the contents of column T? The range
changes each month, and I thought it would be better to change the name range
monthly, rather than the cells in 100+ formulas.

TIA
Carole O
 
B

Biff

Hi!

To me:

=SUMIF('SHIFT 1'!$L$1:$L$147,"BB",'SHIFT 1'!$T$1:$T$147)

is a lot "simpler" than this:

You can use the INDEX function like this:

=SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))

The above example also assumes that column L is the first column in the
named range and column T is the second column in the named range.

If you don't know for sure which column numbers to use then you can
complicate things even further and use a MATCH function to find the column
for you:

=SUMIF(INDEX(sim1,,MATCH("name",A1:L1,0)),"BB",INDEX(sim1,,MATCH("time",A1:L1,0)))

Where "name" and "time" are the coulmn headers. "Name" is the column that
would contain "BB" and "time" is the column that contains the numbers to
SUMIF.

Sometimes named ranges aren't the best way to go!

Biff
 
C

Carole O

Thanks, Biff -

I added another left paren to the Index function, and it works perfectly.

Just what I needed!!

Carole O
 
B

Biff

I added another left paren to the Index function

Don't know why you would have needed to do that!

Anyhow, glad to be of help! Thanks for the feedback.

Biff
 
D

Dave Peterson

I bet Carole was writing about this one:

=SUMIF(INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
that became:
=SUMIF((INDEX(sim1,,1)),"BB",INDEX(sim1,,2))
or removing the extra ")"
=SUMIF(INDEX(sim1,,1),"BB",INDEX(sim1,,2))
 
C

Carole O

That's the one! I'm so grateful for this forum! No one else at work does
what I do with Excel, so it's nice to have someone to ask.

Carole O
 
Top