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

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

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

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

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

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.