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@


--
doug1
------------------------------------------------------------------------
doug1's Profile: http://www.excelforum.com/member.php...o&userid=30746
View this thread: http://www.excelforum.com/showthread...hreadid=522387

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

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

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