Vlookup sum help

[ADAMC]

Hi all thanks in advance for any help.

What I want to do:

Look for a value in A1 of sheet 2 in column A of sheet one and when it finds it sum column O/P/Q of the same row.

I thought like this:

=vlookup(A2,Sheet1!A:D,1,false,sum(Sheet1!O2:Q2)

or this:

=VLOOKUP(A2,SUM(Sheet1!O2:Q2),1,FALSE)

I have tried to figure it out just cant seem to get it t work can anyone help please? I am so confused

Thanks

 

[Joe4]

I think you want a SUMIF or SUMIFS, not a VLOOKUP.
See:
https://www.techonthenet.com/excel/formulas/sumif.php
https://www.techonthenet.com/excel/formulas/sumifs.php

These sum the columns you designate based on some condition(s) being met (matching, for instance).

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	1														1	1	2
2	2														1	1	3
3	3														1	1	1
4	4														1	1	1
5	5														1	1	1
6	6														1	1	1
7	7														1	2	1
8	8														1	1	1
9	9														1	1	1
Sheet1

Excel 2010
	A	B	C	D
1	7		4	Sum
Sheet2

Cell Formulas
Range	Formula
C1	=SUMPRODUCT(INDEX(Sheet1!O1:Q8,MATCH(A1,Sheet1!A1:A9,0),0))

 

[Marcelo Branco]

See if this example helps

Sheet1

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][td="bgcolor: #DCE6F1"]

I​

[/td][td="bgcolor: #DCE6F1"]

J​

[/td][td="bgcolor: #DCE6F1"]

K​

[/td][td="bgcolor: #DCE6F1"]

L​

[/td][td="bgcolor: #DCE6F1"]

M​

[/td][td="bgcolor: #DCE6F1"]

N​

[/td][td="bgcolor: #DCE6F1"]

O​

[/td][td="bgcolor: #DCE6F1"]

P​

[/td][td="bgcolor: #DCE6F1"]

Q​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Names​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

val1​

[/td][td]

val2​

[/td][td]

val3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

10​

[/td][td]

20​

[/td][td]

30​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

12​

[/td][td]

22​

[/td][td]

32​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mary​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

14​

[/td][td]

24​

[/td][td]

34​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Robert​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

16​

[/td][td]

26​

[/td][td]

36​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Richard​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]

18​

[/td][td]

28​

[/td][td]

38​

[/td][/tr]
[/table]

Sheet2

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Names​

[/td][td]

Total​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

John​

[/td][td]

60​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Bob​

[/td][td]

66​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Mary​

[/td][td]

72​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Robert​

[/td][td]

78​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td]

Richard​

[/td][td]

84​

[/td][/tr]
[/table]

Formula in B2 copied down
=SUM(INDEX(Sheet1!O:Q,MATCH(A2,Sheet1!A:A,0),0))

M.

 

[ADAMC]

They work great,

thank you so much!!

So I know what the SUM part does but the rest I don't understand don't suppose you could explain how it works please?

I notice one uses 'SUM' and one uses 'SUMPRODUCT' what's the difference?

Thanks!

 

[Scott T]

Index is a lookup function for more information see
https://support.office.com/en-us/article/index-function-a5dcf0dd-996d-40a4-a822-b56b061328bd

Here it is used to return the values you want to sum. The column augment is 0 so index returns all columns.

Sum only sums numbers sumproduct will sum the products. For example here it will multiply quantity by cost and then sum the results. If there is only one array then it will only sum. sumproduct can handle array formulas with out needing CTRL+SHIFT+ENTER so it can be used to avoid CSE.

Excel 2010
	A	B	C	D
1	Quantity	cost
2	4	7	28
3	6	1	6
4
5			34	Total
6
7
8			34	with sumproducts
Sheet5

Cell Formulas
Range	Formula
C2	=A2*B2
C3	=A3*B3
C5	=SUM(C2:C4)
C8	=SUMPRODUCT(A2:A3,B2:B3)

 

[ADAMC]

That's great, I better get reading.

Thanks.