Vlookups + SUM

[Lmaonade]

Hi,

Is there a way to use VLOOKUPS to find a selection of the same reference and add them together to give you a result?

For example.

I've got this list of Holidays that need to be covered.

In Colomn L I have Vlookups that are taking data from another table. In this table the first colomn is "Contract number covered" So it will match the contract number in this table. Now this would be easy if it was just one cover for each holiday but I have multiple covers for each holiday. For instance... Contract #10001 has 3 different covers, so there will be a list in my 2nd table that have 3 rows using contract #10001 , one has 14 days covered the other 2 have 3 days covered each, but it's only pulling data from the first cover which is 14 days.

So my VLookup in L Column is "=VLOOKUP([@[Contract'#]],Table2,10,FALSE)"

Is there a formula to use to calculate any matching contract number together to give a total?

Thanks in advance,
Andy

 

[JonXL]

Hi,

Is there a way to use VLOOKUPS to find a selection of the same reference and add them together to give you a result?

For example.

I've got this list of Holidays that need to be covered.

In Colomn L I have Vlookups that are taking data from another table. In this table the first colomn is "Contract number covered" So it will match the contract number in this table. Now this would be easy if it was just one cover for each holiday but I have multiple covers for each holiday. For instance... Contract #10001 has 3 different covers, so there will be a list in my 2nd table that have 3 rows using contract #10001 , one has 14 days covered the other 2 have 3 days covered each, but it's only pulling data from the first cover which is 14 days.

So my VLookup in L Column is "=VLOOKUP([@[Contract'#]],Table2,10,FALSE)"

Is there a formula to use to calculate any matching contract number together to give a total?

Thanks in advance,
Andy

It sounds like you want to use a SUMIFS function.

Without knowing exactly what columns all your data are in, the basic formula would look like:

=SUMIFS(column to add,column containing contact #s ,specific contact # to match)

 

[Lmaonade]

Hi Jon,

Thanks for the reply.

Ok so in the first table where I'm trying to pull data into I have this.

then in the 2nd table in sheet2, I have this. Which is where the covers will be listed.

So for instance. In cell L6 in my first table I want it to add the 2 covers from the 2nd table. So this should be 11.

 

[Lmaonade]

Sorry, not sure why that came out so small and blurry,

Here is the 1st picture:
<a href="https://ibb.co/n3xZt7"><img src="https://preview.ibb.co/gc9Q0n/Capture5.jpg" alt="Capture5" border="0"></a><br />

Here is the 2nd picture:
<a href="https://ibb.co/jEz8Ln"><img src="https://preview.ibb.co/cmSHD7/Capture6.jpg" alt="Capture6" border="0"></a><br />

 

[Lmaonade]

Ah, so I think I may have got it.

Does this look correct?

=SUMIFS('Sheet2'!K:K,'Sheet2'!B:B,[@[Contract'#]])

 

[JonXL]

Ah, so I think I may have got it.

Does this look correct?

=SUMIFS('Sheet2'!K:K,'Sheet2'!B:B,[@[Contract'#]])

Well... does it work?

I've never used the @ method of referencing in excel, but assuming it refers to a single cell's value, then I don't see any reason why your formula wouldn't return the results you're looking for.