Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: IF formula has #VALUE! error

  1. #1
    New Member
    Join Date
    Mar 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  3. #3
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •