#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 124
Default ?? 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default ?? 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,388
Default ?? 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Vlookup in vlookup - taking the result as array name SupperDuck Excel Worksheet Functions 2 June 2nd 07 11:05 AM
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP buffgirl71 Excel Discussion (Misc queries) 12 November 15th 06 12:36 AM
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) MikeR-Oz New Users to Excel 1 March 22nd 06 10:01 AM
IF(AND(val1=VLOOKUP( );val2>=VLOOKUP( );val2<=VLOOKUP( );VLOOKUP( Oso Excel Worksheet Functions 2 January 26th 05 07:56 AM
Vlookup info being used without vlookup table attached? Excel Worksheet Functions 0 January 25th 05 11:43 AM


All times are GMT +1. The time now is 03:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"