Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Calculations
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Calculations
To total, try SUMIF,
eg in B2, copied down: =SUMIF('2-16'!$A$2:$A$10000,A2,'2-16'!$D$2:$D$10000) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RoadKill" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Calculations
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:D which also allows you to support addtional rows without having to change the formula. Regards, Fred. "RoadKill" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Calculations
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? "RoadKill" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Vlookup Calculations
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) -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "RoadKill" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Calculations | Excel Worksheet Functions | |||
N/A results on vlookup with time calculations | Excel Worksheet Functions | |||
IRR Calculations | Excel Discussion (Misc queries) | |||
Help with calculations | Excel Worksheet Functions | |||
Calculations Help! | Excel Discussion (Misc queries) |