# VLOOKUP and SUMing a column based on it

#### joj

##### New Member
Hi all, first time poster!

I have 2 columns, column A runs from 10 down to 1. Column B has some other figures in it.

I want a function where I can put a number in a cell and it will lookup in column A to find the number I put in, lets say it is 8 and then I want it to SUM the values in column B that are equal or higher than the value 8. So for example:

10 13
9 2
8 3
7 6
6 5
5 4
4 5
3 6
2 7
1 8

If I put the value 8 into a cell, I want the adjacent cell to sum up the values for 8,9 and 10 and display 18 in this case.

I had =VLOOKUP(A15,A1:B10,2) which gives me the value 3, but I'm not sure how to get the other values and add em up. I also want to be able to do a further mathmatical function on that number if possible (e.g. multiply the result by 0.7).

Any help pls?

Cheers
JOJ

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### Jonmo1

##### MrExcel MVP
Welcome to the board

Try this
=SUMIF(A1:A100,">=8")*.7

Hope this helps.

#### joj

##### New Member
Thanks for the reply Jon, but unless I'm being stupid, doesn't that just sum the values in A? I want to SUM the values in B based on whats in A.

JOJ

#### Jonmo1

##### MrExcel MVP
OK, so you want to sum column B, where Column A is >= 8 ?

=SUMIF(A1:A100,">=8",B1:B100)*.7

#### joj

##### New Member
That works great, but one final refinement. What if I want to use a cell value as the selection criteria? i.e. it might not always be 8, I want to be able to input a value and it work it out.

=SUMIF(A1:A100,">=C1",B1:B100)*.7

doesn't seem to work...or without the quotes

JOJ

#### Simon Lloyd

##### Well-known Member
You need to use sumproduct like this:
=SUMPRODUCT((\$A\$1:\$A\$20=A1)*(\$B\$1:\$B\$20=B1))

#### Jonmo1

##### MrExcel MVP
=sumif(a1:a100,">="&c1,b1:b100)*.7

#### joj

##### New Member
that will do nicely. thank you!

Replies
12
Views
372
Replies
2
Views
208
Replies
22
Views
566
Replies
1
Views
67
Replies
19
Views
459

1,190,859
Messages
5,983,264
Members
439,833
Latest member
CDaviess

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back