Multiple sumif & vlookup formula to combine info and return result in seperate sheet

Hi! It's my first time posting. Though I've searched the forum for a
possible answer - I was only to able to somehow get close to what I'm
looking for - though I'm not sure if what I'm trying to do is even
possible, especially since it requires multiple lookups - I guess?!

In cells B3, B4, and B5 (pls see attached file), I've tried using the following formula to match the
'Reference' numbers to amounts in Sheet 2: D2 using the sumif formula

Please Login or Register  to view this content.

.
Now this worked fine. However here comes the difficult part: the year and
codes. Sheet 2 contains multiple year references, and I'm trying to compile
all of them in Sheet 1. Meaning all amounts which fall under 2009 ['Sheet
1'!B2] & have reference '1.2' are summed up in 'Sheet 1'!B3.

Moreover since the list I'm talking about is very extensive, each Reference
can have multiple Codes. For example, Reference 1.3 can have codes 150 and
450 (or many more). All these need to summed up in Sheet 1.

I don't think VBA is required for this, or is it? I would have thought that
a multiple sumif could be used? or sumif including vlookup? Getting a little lost here

Would appreciate any guidance.

Thanks a ton.

I don't see where you are using the 'Code' values, so I don't know how to add that to the formula.

But, to do the reference/year info you were trying to do, try this formula in B3:

=SUMPRODUCT(--(Sheet2!$B$2:$B$5=$A3),--(Sheet2!$A$2:$A$5=B$2),(Sheet2!$D$2:$D$5))

You can also do that as an array formula (I can give you that formula if you want)...

Thanks Minchazo! I can't believe I forgot the sumproduct!!
It worked out! Many thanks! Though the only problem is, that the file takes about 3 min to load up now (since I have about 150 rows and 30 columns containing the formula). In your view, is there any way to shorten the formula?

If you're using Excel 2007 you could try using SUMIFS instead of SUMPRODUCT

=SUMIFS(Sheet2!$D$2:$D$5,Sheet2!$B$2:$B$5,$A3,Sheet2!$A$2:$A$5,B$2)

It may be quicker.