Referencing multiple criteria to pull data
Hello, I am having a problem getting a formula to work. I have a Master data list as as a stand alone file, configured as such (column headings)...Month, Group Name, Data. I originally set up the data in this structure to easily pull into a pivot table. Looks like: _Month_ __Group_Name_ _Data_ Jan Sales $434 Jan R&D $123 Feb Sales $321 Feb R&D $225 Now, I have another file, which I want to reference and select specific data based upon the first two columns (in this case it is month and Group Name). So, if I want the month of January and the Group Name of Sales in my new workbook, I want it to go to the other file and pull the data, which in this case is from the third column ($434). Therefore, I want a formula that say if it matches "Jan" and "Sales" pull $434. Sounds easy enough, but I can not get it to work properly as the "match" statement is specific to a particular row and the Vlookup statment has problems if I wanted to pull Feb data. Or at least it seems to be the problem the way I am using the functins. Any help would be greatly appreciated. Thanks@:cool: -- doug1 ------------------------------------------------------------------------ doug1's Profile: http://www.excelforum.com/member.php...o&userid=30746 View this thread: http://www.excelforum.com/showthread...hreadid=522387 |
Referencing multiple criteria to pull data
Try to use this formula: =SUMPRODUCT((Sheet1!A$1:A$4="Feb")*(Sheet1!B$1:B$4 ="Sales")*(Sheet1!C$1:C$4)) -- Irina ------------------------------------------------------------------------ Irina's Profile: http://www.excelforum.com/member.php...fo&userid=8685 View this thread: http://www.excelforum.com/showthread...hreadid=522387 |
Referencing multiple criteria to pull data
Thank you for the post, but will this search a range of data and reference a specific cell based upon the first two sets of criteria? Irina Wrote: Try to use this formula: =SUMPRODUCT((Sheet1!A$1:A$4="Feb")*(Sheet1!B$1:B$4 ="Sales")*(Sheet1!C$1:C$4)) -- doug1 ------------------------------------------------------------------------ doug1's Profile: http://www.excelforum.com/member.php...o&userid=30746 View this thread: http://www.excelforum.com/showthread...hreadid=522387 |
Referencing multiple criteria to pull data
Thanks! It worked like a charm....only question I have is why? I am not grasping how this formula performs this function as it looks like it is suppose to sum a column vs. looking up a reference. Can you help explain the formula? I like to understand the mechanics of how new formulas work. :) At any rate...Thank you so much. -- doug1 ------------------------------------------------------------------------ doug1's Profile: http://www.excelforum.com/member.php...o&userid=30746 View this thread: http://www.excelforum.com/showthread...hreadid=522387 |
All times are GMT +1. The time now is 10:46 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com