Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?? VLookup ??
I am using Excel 2007 with which I have 2 spreadsheets, spreadsheet 1 has 3
columns, Customer name, Date and Amount, each customer may have several entries for any given month and the date field goes back several years. Spreadsheet 2 lists all the customer names from spreadsheet 1 in column A, and the other columns have a month and year i.e. Dec 07, Jan 08, Feb 08 etc. What I need to do is create a formula in spreadsheet 2 that will sum all the customer entries for each month. I thought VLookup would work but I cant figure out how to make it work with the month requirement. Thanks for your help |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?? VLookup ??
Are those months and years in sheet2 in Excel date format with the
cell formatted to show just the month and date, or are they text values? If the former then you can use something like this in cell C2 of Sheet2: =SUMPRODUCT((Sheet1!A:A=A2)*(MONTH(Sheet1!B:B)=MON TH(B2))*(YEAR(Sheet1! B:B)=YEAR(B2)*(Sheet1!C:C)) You can use full-column references with Sumproduct in Excel 2007. Copy this down as required. Hope this helps. Pete On May 30, 12:51*am, Charles wrote: I am using Excel 2007 with which I have 2 spreadsheets, spreadsheet 1 has 3 columns, Customer name, Date and Amount, each customer may have several entries *for any given month and the date field goes back several years. Spreadsheet 2 *lists all the customer names from spreadsheet 1 in column A, and the other columns have a month and year i.e. Dec 07, Jan 08, Feb 08 etc. What I need to do is create a formula in spreadsheet 2 that will sum all the customer entries for each month. I thought VLookup would work but I cant figure out how to make it work with the month requirement. Thanks for your help |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
?? VLookup ??
Hi,
Sheet 1: Assuming that your row headers are in row 1 and your data starts in row 2 Sheet 2: Assuming dates are in Row 1 Try this in cell B2 on sheet 2. =SUMPRODUCT((Sheet1!$A$2:$A$30=Sheet2!$A2)*(MONTH( Sheet1!$B$2:$B$30)=MONTH(Sheet2!B$1))*(YEAR(Sheet1 !$B$2:$B$30)=YEAR(Sheet2!B$1))*(Sheet1!$C$2:$C$30) ) Fill across and down. This only allows for a Sheet 1 table down to row 30. You will have to change this (in 4 places) to accommodate the size of your actual table. Regards - Dave. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |