| 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! |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, SUMPRODUCT, or SUMIF?
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 -- A hint to posters: Specific, detailed questions are more likely to be answered than questions that provide no detail about your problem. "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 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! |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, SUMPRODUCT, or SUMIF?
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) -- Regards, Dave "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 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! |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, SUMPRODUCT, or SUMIF?
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! |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 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! |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
VLOOKUP, SUMPRODUCT, or SUMIF?
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! -- Thanks so much! "Teethless mama" wrote: =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 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! |
| Reply |
| 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 | |||
Linear Mode
