+ Reply to Thread
Results 1 to 6 of 6

Formula: Taking a value from a cell based on criteria of another and replacing rest with 0

  1. #1
    Registered User
    Join Date
    11-14-2010
    Location
    Wales
    MS-Off Ver
    Excel 2000
    Posts
    28

    Formula: Taking a value from a cell based on criteria of another and replacing rest with 0

    Hello

    I was hoping that I could ask for some help please. I have uploaded an example spreadsheet to help demonstrate what I am attempting to do.

    I need to work out a formula that takes the values from the 'Goal 1' (column B) - only for the country 'Australia' (column A) and copies those values in the corresponding 'Result' cell (column E). For every other country other than Australia, I need it to fill the cell with a 0.

    I have populated that column manually with the result I am looking for.

    Please can someone advise? what formula that I can use to achieve the same result?

    Any help is very much appreciated.

    Regards
    Debbie
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Formula: Taking a value from a cell based on criteria of another and replacing rest wi

    Try

    =IF([@Country]="Australia",[@[Goal 1]],0)

  3. #3
    Forum Contributor
    Join Date
    04-11-2014
    Location
    Norway
    MS-Off Ver
    Office 365 version 2302
    Posts
    152

    Re: Formula: Taking a value from a cell based on criteria of another and replacing rest wi

    Quote Originally Posted by Hypnopoison View Post
    Hello

    I was hoping that I could ask for some help please. I have uploaded an example spreadsheet to help demonstrate what I am attempting to do.

    I need to work out a formula that takes the values from the 'Goal 1' (column B) - only for the country 'Australia' (column A) and copies those values in the corresponding 'Result' cell (column E). For every other country other than Australia, I need it to fill the cell with a 0.

    I have populated that column manually with the result I am looking for.

    Please can someone advise? what formula that I can use to achieve the same result?

    Any help is very much appreciated.

    Regards
    Debbie
    You want the same value in column E as in column B?

    Then you can put this formula in E2: =B2 and just autocopy it down to E13

  4. #4
    Registered User
    Join Date
    11-14-2010
    Location
    Wales
    MS-Off Ver
    Excel 2000
    Posts
    28

    Re: Formula: Taking a value from a cell based on criteria of another and replacing rest wi

    Hi goranimo, thanks for you reply. Only when the country is Australia, otherwise I'd want the value of 0 in the corresponding cell. John's solution above did the trick. Thanks for your help.

  5. #5
    Registered User
    Join Date
    11-14-2010
    Location
    Wales
    MS-Off Ver
    Excel 2000
    Posts
    28

    Re: Formula: Taking a value from a cell based on criteria of another and replacing rest wi

    Hi John, thanks for the super-speedy response. Your solution was bang on the money - thank you very much indeed :-)

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft? Excel? for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,002

    Re: Formula: Taking a value from a cell based on criteria of another and replacing rest wi

    Thank you for the feedback. If your query has been answered could you please mark the thread as SOLVED ("Thread Tools" at top of first post).

+ 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. replacing the value of a cell with another cell based on a criteria
    By nazaninibb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 08-11-2015, 12:01 AM
  2. Replies: 9
    Last Post: 04-18-2013, 09:27 AM
  3. Macro : To find word and Select rest after this cell and delete rest
    By Zortabello in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-08-2010, 08:06 AM
  4. removing or replacing a row based on specific criteria
    By abuchan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-08-2010, 05:02 PM
  5. formula taking 2 criteria?
    By filanbill in forum Excel General
    Replies: 1
    Last Post: 03-14-2007, 07:59 PM
  6. Taking totals based on criteria. Is this possible?
    By BigBas in forum Excel General
    Replies: 4
    Last Post: 02-24-2007, 03:30 PM
  7. [SOLVED] Based on one cell criteria to change the rest of that row to a col
    By Rochelle B in forum Excel General
    Replies: 7
    Last Post: 11-12-2005, 10:50 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1