VLOOKUP and SUMing a column based on it

[joj]

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

 

[Jonmo1]

Welcome to the board

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

Hope this helps.

 

[joj]

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]

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

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

 

[joj]

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]

You need to use sumproduct like this:
=SUMPRODUCT(($A$1:$A$20=A1)*($B$1:$B$20=B1))

 

[Jonmo1]

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

 

[joj]

that will do nicely. thank you!