+ Reply to Thread
Results 1 to 11 of 11

Trying to get an IF formula to show more than one answer

  1. 10-10-2013, 09:05 AM #1
    c.drysdale89 is offline
    Registered User
    Join Date
    05-09-2012
    Location
    reading
    MS-Off Ver
    2010
    Posts
    32

    Trying to get an IF formula to show more than one answer

    Hi, Hope this explanation below makes sense, I have been wracking my brains trying to figure out if there is any formula I can use!

    I have attached the spreadsheet which should help too, but basically I would like a formula to show the projects codes which still have amounts to recognise, which is also dependent on their 'Class'.

    So for example:

    Class A's with amounts left to recognise are: PM38H, PM04G, PM55G, PM44G, PM99F.


    I thought something like =SUMIFS(A:A,B:B,"A",E:E,>0) would be good, but I don't need a sum, I would like all of the 'answers' to somehow show? Is there anyway to do this or is it easier to just do it manually?

    Thanks in advance! Claire
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-10-2013, 09:11 AM #2
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Trying to get an IF formula to show more than one answer

    Hi

    Do you mean?

    =SUMIF(B:B,"a",E:E)
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.
    Reply With QuoteRegister To Reply
  3. 10-10-2013, 09:13 AM #3
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Trying to get an IF formula to show more than one answer

    Could you not just do a subtraction between col C and col D?
    Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
    Sam Capricci
    Reply With QuoteRegister To Reply
  4. 10-10-2013, 09:24 AM #4
    c.drysdale89 is offline
    Registered User
    Join Date
    05-09-2012
    Location
    reading
    MS-Off Ver
    2010
    Posts
    32

    Re: Trying to get an IF formula to show more than one answer

    Hi, yes that's KIND of what I was expecting. However I don't want a sum of column E to show....

    This is what I basically need a formula for:

    IF it is an 'A' project AND the 'to recognise' amount is more than 0 then show the list of all the project codes?

    Thinking about it, a pivot table may be easier, as I want the Project codes to be the answers, but the 'to recognise' column.... any thoughts?
    Reply With QuoteRegister To Reply
  5. 10-10-2013, 09:34 AM #5
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Trying to get an IF formula to show more than one answer

    So in your example you'll have only 1 result as from all the rows that have "A" in column B, only one has a value>0 in column E.

    ARRAY FORMULA in a cell and copy down.

    Formula: copy to clipboard 
    Please Login or Register  to view this content.




    -- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
    Not just Enter.
    Reply With QuoteRegister To Reply
  6. 10-10-2013, 09:40 AM #6
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Trying to get an IF formula to show more than one answer

    So, would something like this work? =IF(AND(B3="A",C3>D3),C3-D3,"")

    EDIT: if you just want the project code in col A, you can replace the "C3-D3" with A3, and leave the rest alone.
    Last edited by Sam Capricci; 10-10-2013 at 09:44 AM.
    Reply With QuoteRegister To Reply
  7. 10-10-2013, 09:44 AM #7
    c.drysdale89 is offline
    Registered User
    Join Date
    05-09-2012
    Location
    reading
    MS-Off Ver
    2010
    Posts
    32

    Re: Trying to get an IF formula to show more than one answer

    Hi, thanks for the replies, but I want the Project Codes to be the answers, not the sums. I think it will be easier if I just create a pivot table based on the class and whether they have recognised amounts.

    Thanks anyway
    Reply With QuoteRegister To Reply
  8. 10-10-2013, 09:46 AM #8
    Sam Capricci is offline
    Forum Expert Sam Capricci's Avatar
    Join Date
    06-14-2012
    Location
    Palm Harbor, Florida
    MS-Off Ver
    16.95 for Mac MS 365
    Posts
    8,682

    Re: Trying to get an IF formula to show more than one answer

    See my edit... re changing the formula to this...
    =IF(AND(B3="A",C3>D3),A3,"") seems to work unless there is something I'm missing (and it wouldn't be the first time).
    Reply With QuoteRegister To Reply
  9. 10-10-2013, 09:47 AM #9
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Trying to get an IF formula to show more than one answer

    Ok. Maybe a pivot table will be better idea BUT my formula also gives what you asked for? DID you checked it at all?
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  10. 10-10-2013, 09:53 AM #10
    c.drysdale89 is offline
    Registered User
    Join Date
    05-09-2012
    Location
    reading
    MS-Off Ver
    2010
    Posts
    32

    Re: Trying to get an IF formula to show more than one answer

    Thanks Fotis, and Sambo Kid! It seems they do work - I was just being impatient!
    kind regards,c
    Reply With QuoteRegister To Reply
  11. 10-10-2013, 09:55 AM #11
    Fotis1991 is offline
    Forum Expert Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Trying to get an IF formula to show more than one answer

    You are welcome and thanks for the feed back.

    As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.

    Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. formula to show a text answer
    By paulaustralia in forum Excel General
    Replies: 2
    Last Post: 08-24-2010, 11:35 PM
  2. Formula to show combine answer and text in 1 cell from 2 different
    By esupply in forum Excel General
    Replies: 3
    Last Post: 10-27-2009, 03:51 PM
  3. can excel show only the answer to a formula in the cell
    By pmms in forum Excel General
    Replies: 3
    Last Post: 08-03-2006, 12:35 PM
  4. [SOLVED] Need a formula to calculate greater than & show answer as a %
    By dnice66 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-28-2006, 02:10 AM
  5. [SOLVED] How to show the formula and the answer?
    By Susan in forum Excel General
    Replies: 3
    Last Post: 03-30-2006, 08:35 PM

Tags for this Thread

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1