Calculate sum of values based on a ref value, how?

[Cam]

Hi,

I need to calculate the total value based on the lookup value, but not sure
what function to use. The lookup ref data is from row 2 to 7. The calculated
data is B10 which equal 36 (2+34) (sum of column B in row 2 & 6). Thanks

Here's the sample data.
A B C D E
1 WC 2/1 2/2 2/3 2/4
2 100 2 30 11 10
3 150 21 24 15 13
4 205 5 32 14 12
5 300 25 30 11 10
6 100 34 10 21 40
7 150 14 30 11 43
8
9
10 100 36
11 150
12 205
13 300

NOTE: the left most value is just a row#, not actual data for this example
purpose.

 

[Dave Peterson]

=sumif(a$2:a$7,$a10,b$2:b$7)

Hi,

I need to calculate the total value based on the lookup value, but not sure
what function to use. The lookup ref data is from row 2 to 7. The calculated
data is B10 which equal 36 (2+34) (sum of column B in row 2 & 6). Thanks

Here's the sample data.
A B C D E
1 WC 2/1 2/2 2/3 2/4
2 100 2 30 11 10
3 150 21 24 15 13
4 205 5 32 14 12
5 300 25 30 11 10
6 100 34 10 21 40
7 150 14 30 11 43
8
9
10 100 36
11 150
12 205
13 300

NOTE: the left most value is just a row#, not actual data for this example
purpose.

 

[Niek Otten]

In B10:

=SUMIF($A$2:$A$7,"="&$A10,B$2:B$7)

Copy down and to the right

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi,
|
| I need to calculate the total value based on the lookup value, but not sure
| what function to use. The lookup ref data is from row 2 to 7. The calculated
| data is B10 which equal 36 (2+34) (sum of column B in row 2 & 6). Thanks
|
| Here's the sample data.
| A B C D E
| 1 WC 2/1 2/2 2/3 2/4
| 2 100 2 30 11 10
| 3 150 21 24 15 13
| 4 205 5 32 14 12
| 5 300 25 30 11 10
| 6 100 34 10 21 40
| 7 150 14 30 11 43
| 8
| 9
| 10 100 36
| 11 150
| 12 205
| 13 300
|
| NOTE: the left most value is just a row#, not actual data for this example
| purpose.
|
|
|
|

 

[PCLIVE]

Another way:

=SUMPRODUCT(--($A$2:$A$7=A10),$B$2:$B$7)

Regards,
Paul

 

[Dave Peterson]

I see that I missed a couple of $'s.

=sumif($a$2:$a$7,$a10,b$2:b$7)