Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Is it possible to embed a conditional statement inside of a VLOOKUP formula?
For example, if I have the following data: "Stage" "Date" "Revenue" "Term" "WeightedValue" Closed Jan $4,500,000 3 $337,500 Verbal Feb $1,200,000 1 $174,545 Proposal Mar $973,800 3 $12,984 Proposal Mar $2,300,000 3 $30,667 Qualif May $1,000,000 1 $- Qualif Jul $1,000,000 1 $- Proposal Feb $60,000,000 5 $872,727 Qualif Mar $336,000 1 $3,360 Qualif Feb $40,000 1 $727 My objective is to sum the "WeightedValue" for each row that matches the following criteria: 1 - Stage = Proposal 2 - Date = Feb Thank you, Raphael |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Param inputs
In G2: Proposal In H2: Feb Then in I2: =SUMPRODUCT((TRIM(A$2:A$10)=TRIM(G2))*(B$2:B$10=H2 ),E$2:E$10) TRIM for col A & G is used here as a precaution since I noticed there were some data inconsistencies (extra white spaces) in your source col A. You can drop the TRIM if the data is ok. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Raphael" wrote: Is it possible to embed a conditional statement inside of a VLOOKUP formula? For example, if I have the following data: "Stage" "Date" "Revenue" "Term" "WeightedValue" Closed Jan $4,500,000 3 $337,500 Verbal Feb $1,200,000 1 $174,545 Proposal Mar $973,800 3 $12,984 Proposal Mar $2,300,000 3 $30,667 Qualif May $1,000,000 1 $- Qualif Jul $1,000,000 1 $- Proposal Feb $60,000,000 5 $872,727 Qualif Mar $336,000 1 $3,360 Qualif Feb $40,000 1 $727 My objective is to sum the "WeightedValue" for each row that matches the following criteria: 1 - Stage = Proposal 2 - Date = Feb Thank you, Raphael |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Yes, it's possible regardless of a Vlookup formula. You want something like:
=sumproduct(--(a2:a1000="Proposal"),--(b2:b1000="Feb"),e2:e1000) If you have dates in column B, rather than text, use: =sumproduct(--(a2:a1000="Proposal"),--(text(b2:b1000,"mmm")="Feb"),e2:e1000) Regards, Fred. "Raphael" wrote in message ... Is it possible to embed a conditional statement inside of a VLOOKUP formula? For example, if I have the following data: "Stage" "Date" "Revenue" "Term" "WeightedValue" Closed Jan $4,500,000 3 $337,500 Verbal Feb $1,200,000 1 $174,545 Proposal Mar $973,800 3 $12,984 Proposal Mar $2,300,000 3 $30,667 Qualif May $1,000,000 1 $- Qualif Jul $1,000,000 1 $- Proposal Feb $60,000,000 5 $872,727 Qualif Mar $336,000 1 $3,360 Qualif Feb $40,000 1 $727 My objective is to sum the "WeightedValue" for each row that matches the following criteria: 1 - Stage = Proposal 2 - Date = Feb Thank you, Raphael |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Try this:
=SUMPRODUCT((A2:A100="Proposal")*(B2:B100="Jan")*( E2:E100)) where I have assumed your five columns of data are in A:E, and that your data extends to row 100 - adjust as necessary. Hope this helps. Pete On Jan 22, 12:50*am, Raphael wrote: Is it possible to embed a conditional statement inside of a VLOOKUP formula? * For example, if I have the following data: "Stage" * * * "Date" * * * *"Revenue" * * * * * *"Term" "WeightedValue" Closed *Jan * * *$4,500,000 * * 3 * * * *$337,500 Verbal *Feb * * *$1,200,000 * * 1 * * * *$174,545 Proposal * * * *Mar * * *$973,800 * * * * * * * * * * * 3 * * * *$12,984 Proposal * * * *Mar * * *$2,300,000 * * 3 * * * *$30,667 Qualif *May * * *$1,000,000 * * 1 * * * *$- * Qualif *Jul * * *$1,000,000 * * 1 * * * *$- * Proposal * * * *Feb * * *$60,000,000 * *5 * * * *$872,727 Qualif *Mar * * *$336,000 * * * * * * * * * * * 1 * * * *$3,360 Qualif *Feb * * *$40,000 * * * * * * * * * * * *1 * * * *$727 My objective is to sum the "WeightedValue" for each row that matches the following criteria: 1 - Stage = Proposal 2 - Date = Feb Thank you, Raphael |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Thank you Max. It works well.
Raphael "Max" wrote: Param inputs In G2: Proposal In H2: Feb Then in I2: =SUMPRODUCT((TRIM(A$2:A$10)=TRIM(G2))*(B$2:B$10=H2 ),E$2:E$10) TRIM for col A & G is used here as a precaution since I noticed there were some data inconsistencies (extra white spaces) in your source col A. You can drop the TRIM if the data is ok. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Raphael" wrote: Is it possible to embed a conditional statement inside of a VLOOKUP formula? For example, if I have the following data: "Stage" "Date" "Revenue" "Term" "WeightedValue" Closed Jan $4,500,000 3 $337,500 Verbal Feb $1,200,000 1 $174,545 Proposal Mar $973,800 3 $12,984 Proposal Mar $2,300,000 3 $30,667 Qualif May $1,000,000 1 $- Qualif Jul $1,000,000 1 $- Proposal Feb $60,000,000 5 $872,727 Qualif Mar $336,000 1 $3,360 Qualif Feb $40,000 1 $727 My objective is to sum the "WeightedValue" for each row that matches the following criteria: 1 - Stage = Proposal 2 - Date = Feb Thank you, Raphael |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Retrieve VLOOKUP results based upon a conditional statement
Welcome, pl mark ALL responses which help by clicking the YES buttons (like
the one below). Thanks. -- Max Singapore http://savefile.com/projects/236895 Downloads:22,500 Files:370 Subscribers:66 xdemechanik --- "Raphael" wrote: Thank you Max. It works well. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sum of a named range with values based on vlookup results | Excel Worksheet Functions | |||
Value a cell based on vlookup results | Excel Worksheet Functions | |||
vlookup based on results from autofilter | Excel Worksheet Functions | |||
Vlookup to return results of If statement | Excel Worksheet Functions | |||
CONDITIONAL FORMATTING BASED ON RESULTS IN A CELL | Excel Worksheet Functions |