Vlookup Calculations

[RoadKill]

Hello, I would like to see if it is possible to do a calculation with a
vlookup as we have 10,000 records to sort through every Monday. This is what
my lookup looks like now: =vlookup(A2, '2-16'!$A$2:$F$10000, 4, FALSE).

Right now it just displays the first instance. But there could be as much as
a couple hundred instances so we would like to total them.

 

[Max]

To total, try SUMIF,
eg in B2, copied down:
=SUMIF('2-16'!$A$2:$A$10000,A2,'2-16'!$D$2:$D$10000)

 

[Fred Smith]

Then you want Sumif, as in:

=sumif('2-16'!$A2:$A10000,a2,'2-16'!$D2:$D10000)

If the whole column is devoted to this data, you can shorten the formula by
using A:A and D which also allows you to support addtional rows without
having to change the formula.

Regards,
Fred.

 

[RoadKill]

Thank you both. Now it gets a tad bit more complicated. How about if I want
it to look up two things when doing the calculation. Essentially I want it to
match the case of A2 and then calculate for only the ones that match a
certain case in column 4.

So if A2 in sheet 2-16 equals the A2 in the current sheet, then to add the
totals of column 4 in 2-16 if column 3 in 2-16 equals 'Bill Pay'.

Does this make sense?

 

[Max]

To total for multi-col criteria, one way is via SUMPRODUCT,

Eg in E2, copied down:
=SUMPRODUCT(('2-16'!$A$2:$A$10000=A2)*('2-16'!$C$2:$C$10000="Bill
Pay"),'2-16'!$D$2:$D$10000)