Retrieve VLOOKUP results based upon a conditional statement

R

Raphael

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
 
M

Max

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
 
F

Fred Smith

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.
 
P

Pete_UK

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
 
R

Raphael

Thank you Max. It works well.

Raphael

Max said:
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
Click to expand...
 
Top