Sumif, Vlookup, Index/Match Issue?

**marting** · 08-01-2012, 10:29 PM

Hi there,

I have a simple problem I think, but I can't seem to figure out a good formula to get it going. So in the first column I have letters running down the page and in the 2nd column I have numbers that are associated with each letter. I want to get the sum of these numbers if the letters beside it equal the letters in the 3rd column (B, E, H) so the resulting answer should be 16. It needs to be some kind of array formula I believe. I know I can use vlookup and add the 3 vlookups but it needs to a bit smarter than that. Any help would be much appreciated!

A 12 B
B 4 E
C 6 H
D 9
E 11
F 3
G 2
H 1

**SCLai** · 08-01-2012, 10:35 PM

Hi, you may want to try this:

=B1+IF(C1="",0,INDEX(B:B,MATCH(C1,A:A,0)))

Place this in any column for Row 1, then drag the formula down the column.

Hope this is what you are looking for!

SC

**marting** · 08-01-2012, 10:52 PM

Hi SC,

Thanks for your help. I was hoping for one formula that would go into one cell. Thanks,

**benishiryo** · 08-01-2012, 10:55 PM

hi marting, there are 2 ways you can do it.

1. transpose your data in column C manually to make it horizontal (instead of C1:C3, make it C1:E1). then use:
=SUMPRODUCT((A1:A8=C1:E1)*(B1:B8))

2. use a transpose formula inside an array:
=SUMPRODUCT((A1:A8=TRANSPOSE(C1:C8))*(B1:B8))

2nd formula to confirm with CTRL + SHIFT + ENTER

**vlady** · 08-01-2012, 11:54 PM

if you want array you can do it like this...

=SUM(IF((A1:A8=C1)+(A1:A8=C2)+(A1:A8=C3),B1:B8,0))

or

manually entering "letter" for condition...

=SUM(IF(A1:A8={"B","E","H"},B1:B8,0))

see sample file attached.

adding numbers.xlsx

**marting** · 08-02-2012, 12:43 AM

awesome, thanks everyone, especially benishiryo

Sumif, Vlookup, Index/Match Issue?

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Sumif, Vlookup, Index/Match Issue?

Re: Sumif, Vlookup, Index/Match Issue?

Re: Sumif, Vlookup, Index/Match Issue?

Re: Sumif, Vlookup, Index/Match Issue?

Re: Sumif, Vlookup, Index/Match Issue?

Re: Sumif, Vlookup, Index/Match Issue?

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions