Help with formula

[Guest]

How do I write a formula that will search a worksheet tab for names in a
particular column (in this case A), pull all corresponding values for each
hit from another column (in this case D), and give me the sum of all values
from column D on a different tab. The tab with all of the raw data may
contain a few hundred entries at any given time so I need the formula to
accommodate for that.

I have used the "COUNTA" and "COUNTIF" formulas to search for values in the
past, but now I need to perform the search and give me the values from a
different column.

Any help would be greatly appreciated.

 

[Biff]

Try this:

=SUMIF(Sheet1!A:A,"some_name",Sheet1!D)

Better to use a cell to hold "some_name":

E1 = Ann Coulter

=SUMIF(Sheet1!A:A,E1,Sheet1!D)

Biff

 

[Guest]

Think what you're after can be done via SUMIF, for eg ..

In Sheet1 and Sheet2,
Assume names are listed in A2 down

Then in Sheet1,

Put in B2:
=SUMIF(Sheet2!A:A,A2,Sheet2!D)

Copy B2 down to return the required sum of values
for the names in col A from Sheet2's col D

 

[Guest]

Biff & Max,

Thank you for your quick responses and sorry for the duplicate post, it gave
me an error on the first try.

It now works as expected, but is it possible to select range of cell values
or manually type in more than one name? What I need to do is provide a total
for all values in that D column broken out by the name of the person's
manager. For example, there are six staff who report to the first manager.
When I use the following code, I get the actual results of column D for the
first staff person.
=SUMIF(Sheet1!A:A,A2,Sheet1!D)

When I try to use the following code and select all six staff at once, my
result is 0.
=SUMIF(Sheet1!A:A,A2:A7,Sheet1!D)

I guess I could get the results for each staff individually, then use a
second SUMIF to total those by manager. The problem is there are about 40
different staff and seven managers so it would be a lot easier if I only had
to use 7 formulas.

Thanks again for your help.

 

[Guest]

=SUMIF(Sheet1!A:A,A2:A7,Sheet1!D)

Think we could try something like this:
=SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A100,A2:A7,0)),Sheet1!D2100)

Adapt the ranges to suit (use the smallest range)
Note that we can't use entire col references in SUMPRODUCT

 

[Guest]

Think we could try something like this:

=SUMPRODUCT(--ISNUMBER(MATCH(Sheet1!A2:A100,A2:A7,0)),Sheet1!D2100)

Adapt the ranges to suit (use the smallest range)
Note that we can't use entire col references in SUMPRODUCT

Just to clarify that the above refers to the 2 ranges:
Sheet1!A2:A100, Sheet1!D2100
(the 2 ranges should also be identically sized)

---

 

[Guest]

Max you are a GOD!!!!

It works like a charm. Thanks for your help!!

 

[Guest]

You're welcome, PJZ !
Appreciate the feedback ..