Sum Items in Column B if Item In clumn A corresponds to Page 2.

[Gregory Day]

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?

 

[Gregory Day]

This one nailed it, and oh so elegant!

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

Rick