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",IND EX(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

"Carole O" wrote in message
...
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