Vlookup , more than one condition

[kanelones]

Hi!, maybe you can help me with this question.

I have for example this range of data:

A 1
B 2
C 3
D 4
E 5
F 6
G 7


Need to sum the data corresponding with certain letters on column A ( example data C+data F+data G, should give 16 as result) using vlookup function..

I mean, do i have a way instead of =VLOOKUP(A3,A1:B7,2,FALSE)+VLOOKUP(A6,A1:B7,2,FALSE)+VLOOKUP(A7,A1:B7,2,FALSE), to summarize all the conditions with within a simple line?

I appreciate you help as usual..

Pablo

 

[Gerald Higgins]

If your situation is exactly as described, you could also use something like SUMIF, together with a helper column which includes a flag on whether or not to include it in the SUMIF.

For example

A 1 N
B 2 N
C 3 Y
D 4 N
E 5 N
F 6 Y
G 7 Y

=SUMIF(C1:C7,"Y",B1:B7)

 

[sheetspread]

=sum(sumif($a$1:$a$7,{"c";"f";"g"},$b$1:$b$7))

 

[sheetspread]

You can also substitute a contiguous range for the array of constants.

If non contiguous:

=SUM(SUMIF($A$1:$A$7,CHOOSE({1;2;3},A3,A6,A7),$B$1:$B$7))

(ctrl-shift-enter is needed for both unless you type sumproduct instead of sum)

 

[kanelones]

You can also substitute a contiguous range for the array of constants.

If non contiguous:

=SUM(SUMIF($A$1:$A$7,CHOOSE({1;2;3},A3,A6,A7),$B$1:$B$7))

(ctrl-shift-enter is needed for both unless you type sumproduct instead of sum)

OK, but the data is in diferent columns to the right, not just the next column were the conditions are, sorry i didn't clarify it for you, that's why i need to use vlookup i think...

Tks again

 

[MARZIOTULLIO]

	A​	B​	C​	D​
1​	A	1​		criteria SUM
2​	B	2​		C
3​	C	3​		F
4​	D	4​		G
5​	E	5​
6​	F	6​		16​
7​	G	7​

<tbody>
</tbody>

D6=SUMPRODUCT(SUMIF($A$1:$A$7,$D$2:$D$4,$B$1:$B$7))

 

[sheetspread]

OK, but the data is in diferent columns to the right, not just the next column were the conditions are

Then you can search both dimensions with sumproduct. How do you know which columns? Nonzero?

 

[Gerald Higgins]

Or you could even perhaps use SUMIFS instead of SUMIF.

But it's difficult to say for certain, without seeing an answer to sheetspread's question about "how do you know which columns ?"