Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP, SUMPRODUCT, or SUMIF?
I believe there is any easy solution to my problem but after a few hours of
trying I just havent hit on it yethope 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! |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF,SUMPRODUCT | New Users to Excel | |||
SUMPRODUCT or SUMIF | Excel Discussion (Misc queries) | |||
Which SumProduct Sumif or VLookup? | Excel Discussion (Misc queries) | |||
HELP!!!! sumif or sumproduct ??? :( | Excel Worksheet Functions | |||
I've tried Sumproduct, SumIf, Vlookup and Hlookup. | Excel Worksheet Functions |