IF formula has #VALUE! error

JRRT · Mar 20th, 2002, 11:10 AM

This is a different question than the previous but I typed the following in cell C21 and it says "#VALUE!"
=IF(E3:E17="Pioneer 33r87",B21-D3:D17)
What I would like to do is if the text "pioneer 33r87" is found in cells E3 through E17 then take the number entered in the cell that is to the left of it i.e. D3 and subtract that amount from cell B21. What would the correct formula look like. Thanks.

Mark W. · Mar 20th, 2002, 11:13 AM

Since this formula employees array references, it must be entered as an array formula using Control+Shift+Enter. For more on the entry of array formulas see the Excel Help topic for "About array formulas and how to enter them".

Jay Petrulis · Mar 20th, 2002, 12:15 PM

Hi JRRT,

Alternatively, if you don't want an array formula, use SUMPRODUCT (by definition an array function not entered as such).

=SUMPRODUCT((E3:E17="Pioneer 33r87")*(B21-D3:D17))

HTH,
Jay

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 20th, 2002, 11:10 AM	#1
JRRT New Member Join Date: Mar 2002 Posts: 21	This is a different question than the previous but I typed the following in cell C21 and it says "#VALUE!" =IF(E3:E17="Pioneer 33r87",B21-D3:D17) What I would like to do is if the text "pioneer 33r87" is found in cells E3 through E17 then take the number entered in the cell that is to the left of it i.e. D3 and subtract that amount from cell B21. What would the correct formula look like. Thanks.

Mar 20th, 2002, 11:13 AM	#2
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	Since this formula employees array references, it must be entered as an array formula using Control+Shift+Enter. For more on the entry of array formulas see the Excel Help topic for "About array formulas and how to enter them".

Mar 20th, 2002, 12:15 PM	#3
Jay Petrulis MrExcel MVP Join Date: Mar 2002 Location: Chicago, IL USA Posts: 2,042	Hi JRRT, Alternatively, if you don't want an array formula, use SUMPRODUCT (by definition an array function not entered as such). =SUMPRODUCT((E3:E17="Pioneer 33r87")*(B21-D3:D17)) HTH, Jay