?? VLookup ??

[Charles]

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 can’t
figure out how to make it work with the month requirement.

Thanks for your help

 

[Pete_UK]

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)=MONTH(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

 

[Dave]

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.