Steph,

A pivot table would generally be easiest.

But if you need a formula, use SUMPRODUCT:

=SUMPRODUCT(--("P"=Data!G2:G100),--(A2=Data!H2:H100),Data!M2:M100)

Tim C

"steph" wrote in message
...
I believe there is any easy solution to my problem but after a few hours of
trying I just haven't hit on it yet.hope someone else can.

I have 2 worksheets titled "data" and "jobs".
I need to lookup a value from the "jobs" worksheet on the "data" worksheet
(col H). If I find the value in H, I need to sum the value of col M from
the
"data" worksheet where the rows had a match on col H. So far, so good.
However, I only want to sum the rows with a value of "P" in col G. Here
is
my example:

Data
Col G Col H Col M
P ABC123 100
P DEF456 150
D GHI789 200
D GHI789 250
P GHI789 300
P GHI789 350

Jobs
Col A Col B
ABC123 100
DEF456 150
GHI789 650

I've tried various ways around this but keep getting the full total of
GHI789 = 1100. I've also tried
=VLOOKUP(A2,data!H2:H1911,SUMPRODUCT((data!$G$2:$G $65536="P")*(data!$M$2:$M$65536)))
but this gives me a #REF error. Am I close??

--
Thanks so much!