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

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

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