Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
I was wandering if I could ask for some help with a formula. I have 2 sheets. Sheet 1 has raw data for the month which I have subtotaled using the Data menu. Sheet 2 has the only 4 fields of data that I need. These are Date, Product Name, Volume, and Time. I have 14 products and I wanted to get the total volume for each product per day. I have set up a Match formula in sheet 2 that looks at the volume, looks at each product name, and the date. So far, I have not been able to pull the correct total from the set of data on sheet 1. Any help is greatly appreciated. My sheets are set up the following way: sheet 1 – column C (product name SubTotal), column D (day of the month), column K (volume) Sheet 2 – column B (day of the month), column C4:C17 (product name Total), column D2 (volume) My match formula is as follows: =OFFSET(Sheet2!$D$2,MATCH(C4,'Sheet 1'!C:C,0),MATCH($B$3,'Sheet 1'!D:D,0)) Thank you! -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=564368 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
Take a look at SUMPRODUCT which I think will better meet your need.
A very full explanation is given he http://www.xldynamic.com/source/xld.SUMPRODUCT.html HTH "oakman" wrote: I was wandering if I could ask for some help with a formula. I have 2 sheets. Sheet 1 has raw data for the month which I have subtotaled using the Data menu. Sheet 2 has the only 4 fields of data that I need. These are Date, Product Name, Volume, and Time. I have 14 products and I wanted to get the total volume for each product per day. I have set up a Match formula in sheet 2 that looks at the volume, looks at each product name, and the date. So far, I have not been able to pull the correct total from the set of data on sheet 1. Any help is greatly appreciated. My sheets are set up the following way: sheet 1 €“ column C (product name SubTotal), column D (day of the month), column K (volume) Sheet 2 €“ column B (day of the month), column C4:C17 (product name Total), column D2 (volume) My match formula is as follows: =OFFSET(Sheet2!$D$2,MATCH(C4,'Sheet 1'!C:C,0),MATCH($B$3,'Sheet 1'!D:D,0)) Thank you! -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=564368 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Function
Thank you for the guidance HTH. The paper looks very complete and suitable. -- oakman ------------------------------------------------------------------------ oakman's Profile: http://www.excelforum.com/member.php...fo&userid=9172 View this thread: http://www.excelforum.com/showthread...hreadid=564368 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MATCH function, exclusion question | Excel Discussion (Misc queries) | |||
rounding numbers for match function | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel | |||
I want to use the MATCH function with the AVERAGE function but I . | Excel Worksheet Functions | |||
Match function selecting first value it matches on exactly | Excel Worksheet Functions |