+ Reply to Thread
Results 1 to 7 of 7

IF AND Not working as it should

  1. #1
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    IF AND Not working as it should

    I can't see what I'm doing wrong here. The formula works in one cell, but not the rest.

    I trying to get it to give me a result if B matches N, and R matches E1:J1, then give me the result in Q.

    In this sample it works in cell I2, but not the rest?

    IF AND PROBLEM.xls

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: IF AND Not working as it should

    I assume there will be only one result in Q based on the 2 criteria?

    Try this in E2 and filled right/down
    =SUMIFS($Q$1:$Q$18,$N$1:$N$18,$B2,$R$1:$R$18,E$1)

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: IF AND Not working as it should

    Try this formula
    in E2 drag formula to the right and then down


    =SUMPRODUCT(($N$1:$N$18=$B2)*($R$1:$R$18=E$1)*($Q$1:$Q$18))
    Attached Files Attached Files
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Valued Forum Contributor Saarang84's Avatar
    Join Date
    02-19-2009
    Location
    Chennai, India
    MS-Off Ver
    XL 2003 to 2010
    Posts
    812

    Re: IF AND Not working as it should

    Try using the below formula as an array formula (confirmed by pressing Ctrl+Shift+Enter)
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    copy down and across for all names across your entire range E:J
    Last edited by Saarang84; 06-12-2014 at 09:48 AM.
    If my assistance has helped, there is a reputation icon * on the left hand corner below the post - you can show your appreciation to the user who has helped in resolving your requirement.

    If your requirement has been solved please mark your thread as Solved.
    In the menu bar above the very first post, select Thread Tools, then select "Mark this thread as Solved".

    Kindly use [FORMULA] or [CODE] tags when posting your code.

    Regards,
    Sarang

  5. #5
    Forum Contributor aganesan99's Avatar
    Join Date
    04-26-2013
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    129

    Re: IF AND Not working as it should

    Hi,

    Please find attached the file. will help you.

    Thanks,
    Aravindhan
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    10-26-2011
    Location
    Ottawa, ON Canada
    MS-Off Ver
    Excel 2011 for Mac
    Posts
    128

    Re: IF AND Not working as it should

    Thanks all! All solutions worked great.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: IF AND Not working as it should

    You're welcome and thank you for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  2. Replies: 4
    Last Post: 09-03-2013, 02:52 AM
  3. Replies: 9
    Last Post: 03-08-2013, 11:50 AM
  4. Replies: 2
    Last Post: 08-17-2012, 08:16 AM
  5. Replies: 1
    Last Post: 01-26-2005, 04:20 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1