VLOOKUP, SUMPRODUCT, or SUMIF?
=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.
"steph" wrote:
I believe there is any easy solution to my problem but after a few hours of
trying I just havent 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
Ive tried various ways around this but keep getting the full total of
GHI789 = 1100. Ive 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!
|