Lookup value throughout an array_return adjacent value and summing
 

Hello. I have a list of 4 names. Those names each have an ID number tagged
next to them. Throughout an array below that I have several (mini-tables)
that allow me to enter the tag and the cell next to the tag populates with
the name associated to that tag. Now, next to that name value is a cell where
I enter a number value for the number of home runs hit. Back at the top of
the page next to the kid's name, I'd like to look up from all of the
(mini-tables) everywhere on the page I see a given name nad somehow add all
of the number values next to the name values.

Summary:

I want one cell to have a formula that looks up the value 'John' in an array
I select and return the number next to that name after summing all occurances.

Thanks in advance!!!

Sounds like you need a SumIf formula. If not, provide a small sample
along with the expected result.

SP wrote:
Hello. I have a list of 4 names. Those names each have an ID number tagged
next to them. Throughout an array below that I have several (mini-tables)
that allow me to enter the tag and the cell next to the tag populates with
the name associated to that tag. Now, next to that name value is a cell where
I enter a number value for the number of home runs hit. Back at the top of
the page next to the kid's name, I'd like to look up from all of the
(mini-tables) everywhere on the page I see a given name nad somehow add all
of the number values next to the name values.

Summary:

I want one cell to have a formula that looks up the value 'John' in an array
I select and return the number next to that name after summing all occurances.

Thanks in advance!!!

EXAMPLE:

NAME TOTAL HOME RUNS
John (formula needed)
Suzy (formula needed)
[all above are in different cells]

(Mini tables)

WEEK1
NAME HRuns
John 1
Suzy 2

WEEK2
NAME HRuns
John 3
Suzy 1

WEEK 3
etc.

Of course, there are multiple columns containing the names and the names are
not always in the same order, but the number of runs is ALWAYS the cell to
the right of the name. (i.e. if name is in B27 the runs for that week is in
C27).

Also, the name is a value from a CHOOSE function (if that makes a difference).

Thanks!

"Aladin Akyurek" wrote:

Sounds like you need a SumIf formula. If not, provide a small sample
along with the expected result.

SP wrote:
Hello. I have a list of 4 names. Those names each have an ID number tagged
next to them. Throughout an array below that I have several (mini-tables)
that allow me to enter the tag and the cell next to the tag populates with
the name associated to that tag. Now, next to that name value is a cell where
I enter a number value for the number of home runs hit. Back at the top of
the page next to the kid's name, I'd like to look up from all of the
(mini-tables) everywhere on the page I see a given name nad somehow add all
of the number values next to the name values.

Summary:

I want one cell to have a formula that looks up the value 'John' in an array
I select and return the number next to that name after summing all occurances.

Thanks in advance!!!

If the expected result is 4, then:

=SUMIF(NameRange,"John",RangeHRuns)

SP wrote:
EXAMPLE:

NAME TOTAL HOME RUNS
John (formula needed)
Suzy (formula needed)
[all above are in different cells]

(Mini tables)

WEEK1
NAME HRuns
John 1
Suzy 2

WEEK2
NAME HRuns
John 3
Suzy 1

WEEK 3
etc.

Of course, there are multiple columns containing the names and the names are
not always in the same order, but the number of runs is ALWAYS the cell to
the right of the name. (i.e. if name is in B27 the runs for that week is in
C27).

Also, the name is a value from a CHOOSE function (if that makes a difference).

Thanks!

"Aladin Akyurek" wrote:


Sounds like you need a SumIf formula. If not, provide a small sample
along with the expected result.

SP wrote:

Hello. I have a list of 4 names. Those names each have an ID number tagged
next to them. Throughout an array below that I have several (mini-tables)
that allow me to enter the tag and the cell next to the tag populates with
the name associated to that tag. Now, next to that name value is a cell where
I enter a number value for the number of home runs hit. Back at the top of
the page next to the kid's name, I'd like to look up from all of the
(mini-tables) everywhere on the page I see a given name nad somehow add all
of the number values next to the name values.

Summary:

I want one cell to have a formula that looks up the value 'John' in an array
I select and return the number next to that name after summing all occurances.

Thanks in advance!!!


--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.