2 IF Formulas (merge together ?)

1. 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*"})),"")

2. 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. 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()

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

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

6. 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. 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. Re: 2 IF Formulas (merge together ?)

This worked - thank you!!!

9. Re: 2 IF Formulas (merge together ?)

Glad to hear it - thanks for the rep.

Pete

10. 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. 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. Re: 2 IF Formulas (merge together ?)

okay sorry.

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

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