+ Reply to Thread
Results 1 to 12 of 12

2 IF Formulas (merge together ?)

  1. #1
    Registered User
    Join Date
    01-16-2019
    Location
    us
    MS-Off Ver
    10
    Posts
    11

    2 IF Formulas (merge together ?)

    I have 2 formulas that work by themselves, BUT need them to merge together into one formula (keep getting errors). Just trying to say if the cell I'm referencing in Column D contains the word "REGION" then do not return a value from it. Otherwise if it IS in the pivot table I am referencing then needs to say "Strategic"

    =IF(COUNTIF(Master_4.21!H$5:$H$43,D14),"Strategic","")

    =IF(SUM(COUNTIF(D14,{"*REGION*"})),"")

    Thanks in advance!!!!

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: 2 IF Formulas (merge together ?)

    Try this:

    =IF(COUNTIF(D14,"*REGION*"),"",IF(COUNTIF(Master_4.21!H$5:$H$43,D14),"Strategic",""))

    Hope this helps.

    Pete

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,730

    Re: 2 IF Formulas (merge together ?)

    or perhaps just...
    =IF(D14="*REGION*","",IF(COUNTIF(Master_4.21!H$5:$H$43,D14),"Strategic",""))

    In the 2nd countif, it looks like you are just testing to see if the contents of D14 are anywhere in that range, no matter how many times? If so, you could also use MATCH()
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: 2 IF Formulas (merge together ?)

    Just a tip, you can have a maximum of 7 'IF' Functions in one formula. You won't get any errors, after 7 it just won't work. I found this after much trial & error, hope this might save you some.
    Remember you are unique, like everyone else

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    40,553

    Re: 2 IF Formulas (merge together ?)

    Not any more - you must have an old version of Excel! From Excel 2007 it went up to 64.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

  6. #6
    Forum Contributor noboffinme's Avatar
    Join Date
    08-29-2013
    Location
    Melbourne, Australia
    MS-Off Ver
    Excel 2003/7/10/13
    Posts
    953

    Re: 2 IF Formulas (merge together ?)

    Good to know AliGW, It was a long time ago but I still remember how frustrating it was to work that one out... Btw, I'm trying to post a thread & I get an error like a Firewall not letting me post?? I wondered if anything had changed at Excel Forum? Thanks again. Peter

  7. #7
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: 2 IF Formulas (merge together ?)

    The firewall kicks in if it thinks you are trying to post some HTML code.

    The angle brackets < and > are used to indicate this, and you could inadvertently have this in a formula where you mean not equal to - you have to remember to include a space immediately before and after those symbols.

    Hope this helps.

    Pete

  8. #8
    Registered User
    Join Date
    01-16-2019
    Location
    us
    MS-Off Ver
    10
    Posts
    11

    Re: 2 IF Formulas (merge together ?)

    This worked - thank you!!!

  9. #9
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: 2 IF Formulas (merge together ?)

    Glad to hear it - thanks for the rep.

    Pete

  10. #10
    Registered User
    Join Date
    04-24-2020
    Location
    Malaysia
    MS-Off Ver
    3
    Posts
    9

    Re: 2 IF Formulas (merge together ?)

    Attachment 677328
    can anyone help with this ?
    i need the right formula to total amount following by date.

  11. #11
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,573

    Re: 2 IF Formulas (merge together ?)

    You should start your own new thread rather than hijack someone else's (see Rule 04 at the top of the screen).

    Pete

  12. #12
    Registered User
    Join Date
    04-24-2020
    Location
    Malaysia
    MS-Off Ver
    3
    Posts
    9

    Re: 2 IF Formulas (merge together ?)

    okay sorry.

+ 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. merge 2 formulas
    By max_max in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-29-2017, 01:09 PM
  2. [SOLVED] Merge Formulas
    By yuenk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2015, 07:16 AM
  3. [SOLVED] Modify 2 formulas, Merge two formulas into one formula
    By score in forum Excel General
    Replies: 7
    Last Post: 01-09-2015, 12:24 PM
  4. [SOLVED] How do I merge these two formulas?
    By nwb in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 09-24-2014, 01:23 PM
  5. [SOLVED] Merge the two formulas together?
    By Dougiebn in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-26-2013, 03:52 PM
  6. How to merge two different formulas
    By hirenhkansara in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-24-2012, 07:50 AM
  7. Merge two formulas
    By Zaeguzah in forum Excel General
    Replies: 3
    Last Post: 11-12-2009, 03:35 PM

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