I really hope you guys can assist me. I need a formula that will add up the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony. The result should equal
51.


Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?

--
Thank you,

Gregory

Try this formula...

=SUMPRODUCT((Sheet2!B1:B20="Level 3")*Sheet1!B1:B20)

Rick


"Gregory Day" wrote in message
...
I really hope you guys can assist me. I need a formula that will add up the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony. The result should
equal
51.


Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?

--
Thank you,

Gregory

I entered the formula as you suggested, changing only the cell numbers. The
result was #Value. Perhaps if should have used the correct ruw number in my
example. (I am confused, I thought the * was for multiplication?)

Summary! (Sheet Name)
A B
22 Adam 15
23 Dave 12
24 David 16
25 Donna 16
26 Gregory 12
27 Jeff 13
28 Steve 16
29 Tony 14
30 Juan 0
31 Hamilton0
32 Jeremy 0

Consultants! (Sheet Name)
A B
4 Adam Level 1
5 Dave Level 3
6 David Level 2
7 Donna Level 1
8 Gregory Level 3
9 Jeff Level 3
10 Steve Level 1
11 Tony Level 3
12 Juan Level 1
13 HamiltonLevel 1
14 Jeremy Level 1

Basically, I just want a total of the values in Sheet1, ColumnB for the
Level 3's?

Does that make sense?

It is always best NOT to simplify your questions when posting... formulas
are very often sensitive to location, so the ones crafted to answer
questions on newsgroups are designed to operate within the ranges provided.
Give this formula a try...

=SUMPRODUCT((Sheet2!B4:B14="Level 3")*Sheet1!B22:B32)

The number of cells covered in both ranges must be the same (in your case,
that is 11 cells), so if you wanted to expand the range covered by this
formula, you must add the same number of cells to both ranges. For example,
if you worksheet added 4 more names under those already existing, then the
formula would become this...

=SUMPRODUCT((Sheet2!B4:B18="Level 3")*Sheet1!B22:B36)

Because of the way the formula is constructed, you can specify a higher
range end than you have data for and the formula will still work (assuming
there is no other data above the range which could accidentally match what
you are searching on). For example, if there is no other data above the data
you showed us, you could use this formula if you wanted....

=SUMPRODUCT((Sheet2!B4:B1014="Level 3")*Sheet1!B22:B1032)

As for you question regarding the asterisk, yes, it is for multiplication.
The SUMPRODUCT function evaluates the ranges provided on a row by row basis,
then, after all rows have been processed, they are added up. In the formula,
(Sheet2!B4:B1014="Level 3") is a array of logical expressions evaluating to
either TRUE or FALSE; but, when used in a mathematical expression, the TRUE
and FALSE is converted to 1 and 0 so the multiplication can be carried out.
When the expression is TRUE (when the row being looked at equal "Level 3"),
a 1 is generated and multiplied by the corresponding value in the other
range... a pure value, not a logical expression. On the other hand, if the
expression is FALSE (the row being looked at is not equal to "Level 3"), a 0
is generated and the product of that with its corresponding row in the other
range evaluates to 0. This means only value in the second range get added if
contents of the corresponding rows in the first range equal "Level 3".

Rick



"Gregory Day" wrote in message
...
I entered the formula as you suggested, changing only the cell numbers. The
result was #Value. Perhaps if should have used the correct ruw number in
my
example. (I am confused, I thought the * was for multiplication?)

Summary! (Sheet Name)
A B
22 Adam 15
23 Dave 12
24 David 16
25 Donna 16
26 Gregory 12
27 Jeff 13
28 Steve 16
29 Tony 14
30 Juan 0
31 Hamilton0
32 Jeremy 0

Consultants! (Sheet Name)
A B
4 Adam Level 1
5 Dave Level 3
6 David Level 2
7 Donna Level 1
8 Gregory Level 3
9 Jeff Level 3
10 Steve Level 1
11 Tony Level 3
12 Juan Level 1
13 HamiltonLevel 1
14 Jeremy Level 1

Basically, I just want a total of the values in Sheet1, ColumnB for the
Level 3's?

Does that make sense?

Try this:

Formula entered on Sheet1.

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:A11,Sheet2!A1:A11,0))),--(Sheet2!B1:B11="Level
3"),B1:B11)

--
Biff
Microsoft Excel MVP


"Gregory Day" wrote in message
...
I really hope you guys can assist me. I need a formula that will add up the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony. The result should
equal
51.


Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?

--
Thank you,

Gregory

Another...
=SUMIF(Sheet2!B1:B11,"=Level 3",Sheet1!B1:B11)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Gregory Day"
wrote in message
I really hope you guys can assist me. I need a formula that will add up the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony.
The result should equal 51.

Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?
--
Thank you,
Gregory

This one nailed it, and oh so elegant!
--
Thank you,

Gregory


"Jim Cone" wrote:

Another...
=SUMIF(Sheet2!B1:B11,"=Level 3",Sheet1!B1:B11)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Gregory Day"
wrote in message
I really hope you guys can assist me. I need a formula that will add up the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony.
The result should equal 51.

Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?
--
Thank you,
Gregory

It did? For the rows you told me you had your data in? Really?

Rick


"Gregory Day" wrote in message
...
This one nailed it, and oh so elegant!
--
Thank you,

Gregory


"Jim Cone" wrote:

Another...
=SUMIF(Sheet2!B1:B11,"=Level 3",Sheet1!B1:B11)
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Gregory Day"
wrote in message
I really hope you guys can assist me. I need a formula that will add up
the
number in Sheet1, ColumnB for each person who has "Level 3" next to their
name on Sheet2. That is Dave, Greg, Jeff, and Tony.
The result should equal 51.

Sheet1
A B
1 Adam 15
2 Dave 12
3 David 16
4 Donna 16
5 Gregory 12
6 Jeff 13
7 Steve 16
8 Tony 14
9 Juan 0
10 Hamilton 0
11 Jeremy 0

Sheet 2
A B
1 Adam Level 1
2 Dave Level 3
3 David Level 2
4 Donna Level 1
5 Gregory Level 3
5 Jeff Level 3
7 Steve Level 1
8 Tony Level 3
9 Juan Level 1
10 Hamilton Level 1
11 Jeremy Level 1

Does anyone have an idea of how this can be done?
--
Thank you,
Gregory