VLOOKUP, SUMPRODUCT, or SUMIF?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

steph - 28 Mar 2007 15:20 GMT

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??

Signature

Thanks so much!

Reply to this Message

Dave F - 28 Mar 2007 15:24 GMT

Seems to me you can juse use a pivot table to create this kind of summary.
See here for more info: http://www.cpearson.com/excel/pivots.htm

Dave

Signature

A hint to posters: Specific, detailed questions are more likely to be
answered than questions that provide no detail about your problem.

> 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.
[quoted text clipped - 24 lines]
> 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??

Reply to this Message

David Billigmeier - 28 Mar 2007 15:32 GMT

Unfortunately SUMPRODUCT() won't work on full columns, you have to end the
reference at row 65535... but this will work:

=SUMPRODUCT(--(Data!$H$1:$H$65535=A1),--(Data!$G$1:$G$65535="P"),Data!$M$1:$M$65535)

Then, for the 65536th row you can add just a simple check:

+if(and(Data!$H$65536=A1,Data!$G$65536="P"),Data!$M$65536,0)

Signature

Regards,
Dave

Reply to this Message

Tim C - 28 Mar 2007 15:33 GMT

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

>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.
[quoted text clipped - 27 lines]
> =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??

Reply to this Message

Teethless mama - 28 Mar 2007 15:36 GMT

=SUMPRODUCT(--($G$2:$G$100="p"),--($H$2:$H$100=A2),$M$2:$M$100)

Drag the the Fill Handle to copy down as far as needed.

Reply to this Message

steph - 28 Mar 2007 16:26 GMT

Thanks to all who responded with the correct SUMPRODUCT formula. I guess I
was close but no cigar!! As for using a pivot table, yes, I am a big fan of
these. But in this particular case, I needed a formula to continue in my
spreadsheet.

Thanks again for all the quick responses!

Signature

Thanks so much!

> =SUMPRODUCT(--($G$2:$G$100="p"),--($H$2:$H$100=A2),$M$2:$M$100)
>
[quoted text clipped - 28 lines]
> > 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??

Reply to this Message