ExcelBanter - Named ranges - column/row question

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Named ranges - column/row question (https://www.excelbanter.com/excel-discussion-misc-queries/25514-named-ranges-column-row-question.html)

Named ranges - column/row question

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

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

Thanks, Biff -

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

Just what I needed!!

Carole O
"Biff" wrote:

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

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

"Carole O" wrote in message
...
Thanks, Biff -

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

Just what I needed!!

Carole O
"Biff" wrote:

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

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))

Biff wrote:

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

"Carole O" wrote in message
...
Thanks, Biff -

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

Just what I needed!!

Carole O
"Biff" wrote:

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

--

Dave Peterson

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

"Dave Peterson" wrote:

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))

Biff wrote:

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

"Carole O" wrote in message
...
Thanks, Biff -

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

Just what I needed!!

Carole O
"Biff" wrote:

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

--

Dave Peterson