+ Reply to Thread
Results 1 to 14 of 14

how to check the adjacent cell of the COUNTIF result in a formula

  1. 08-30-2017, 03:27 AM #1
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    how to check the adjacent cell of the COUNTIF result in a formula

    Hello everyone

    Can someone please help me with this puzzle ? I have this formula to check out in a column if there is a match :

    =IF(AND(COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Agile")=1;COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Message Runner")<=2);"Acrobatics: Message Runner";"")

    In this formula basically the first half is working properly (IF in CharacterSheet tab B1:B20 there is one entry "Acrobatics: Agile"), the problem now is in the second part which should check again in range B1:B20 if there is an entry "Acrobatics: Message Runner" but this time it should check if the adjacent cell to the Left (checking B1:B20 for Acrobatics: Message Runner and then check the cell A? next to it) if the value is equal or below the number 2.

    As it is now, my formula in the second part is checking for double entry of Acrobatics: Message Runner in the range B1:B20 but that shoud never be the case since the Columns A:B in CharacterSheet tab are like this :

    Please Login or Register  to view this content.
    Thank you guys
    Reply With QuoteRegister To Reply
  2. 08-30-2017, 03:41 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    You'll probably need this:

    COUNTIFS(CharacterSheet!B1:B20;"Acrobatics: Message Runner";CharacterSheet!A1:A20;<=2)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 08-30-2017, 03:45 AM #3
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Hello AliGW

    Will this check for the adjacent cell of Acrobatics: Message Runner though ? I mean some values in Column A will be 1 some others will be 2 and some others might even be 5. We need the formula to check Column B for Acrobatics: Message Runner and the adjacent value (if it finds it) to check if it is 2 or less.


    Edit : This addition gives me a formula error .

    =IF(AND(COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Agile")=1;COUNTIFS(CharacterSheet!B1:B20;"Acrobatics: Message Runner";CharacterSheet!A1:A20;<=2);"Acrobatics: Message Runner";"")

    Thank you
    Last edited by ulothar; 08-30-2017 at 03:50 AM.
    Reply With QuoteRegister To Reply
  4. 08-30-2017, 03:47 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Try it and see!

    =IF(AND(COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Agile")=1;COUNTIFS(CharacterSheet!B1:B20;"Acrobatics: Message Runner";CharacterSheet!A1:A20;<=2) ;"Acrobatics: Message Runner";"")
    Reply With QuoteRegister To Reply
  5. 08-30-2017, 03:52 AM #5
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    This is the error I get
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  6. 08-30-2017, 03:56 AM #6
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Which version of Excel are you using? I can't see the image properly - it's fuzzy. Attach the workbook here.

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.
    Reply With QuoteRegister To Reply
  7. 08-30-2017, 03:57 AM #7
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Maybe try this:

    =IF(AND(COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Agile")=1;COUNTIFS(CharacterSheet!B1:B20;"Acrobatics: Message Runner";CharacterSheet!A1:A20;<=2));"Acrobatics: Message Runner";"")
    Reply With QuoteRegister To Reply
  8. 08-30-2017, 04:01 AM #8
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Sure but the project is protected. I will send you the password to unlock the Visual Basic (and from there to remove the scroll area limitation from the tab StartingPage1)

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  9. 08-30-2017, 04:05 AM #9
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    No, sorry - you are making me have to jump through hoops now to help you. Please provide a cut-down, simplified version with just the worksheet in question for me to troubleshoot. I am not going to try to find my way around the workbook you have attached.
    Reply With QuoteRegister To Reply
  10. 08-30-2017, 04:08 AM #10
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    ok give me a second to make a sample file then will take not much time.
    Reply With QuoteRegister To Reply
  11. 08-30-2017, 04:21 AM #11
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Ok here we go
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  12. 08-30-2017, 04:25 AM #12
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Try this:

    =IF(AND(COUNTIF(CharacterSheet!B1:B20;"Acrobatics: Agile")=1;COUNTIFS(CharacterSheet!B1:B20;"Acrobatics: Message Runner";CharacterSheet!A1:A20;"<="&2));"Acrobatics: Message Runner";"")
    Last edited by AliGW; 08-30-2017 at 04:30 AM.
    Reply With QuoteRegister To Reply
  13. 08-30-2017, 04:26 AM #13
    ulothar
    ulothar is offline
    Registered User
    Join Date
    01-13-2017
    Location
    Greece
    MS-Off Ver
    2007
    Posts
    52

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Again same error in the formula :/ maybe in "Acrobatics: Agile")= should be 1 next

    Edit : Yeah now it works needed the 1 after = thanks alot
    Last edited by ulothar; 08-30-2017 at 04:29 AM.
    Reply With QuoteRegister To Reply
  14. 08-30-2017, 04:29 AM #14
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,933

    Re: how to check the adjacent cell of the COUNTIF result in a formula

    Glad to have helped!

    Please mark the thread as SOLVED using the thread tools top right.
    Attached Files Attached Files
    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 that returns adjacent cell depending of result of diferent cells
    By Flip_Flop in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 07-27-2017, 08:50 AM
  2. [SOLVED] If the IF function result is false then check the next cell until get a true result
    By Beag air Bheag in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-10-2017, 08:55 AM
  3. [SOLVED] Formula that will result the cell blank based on adjacent cell
    By LShepherd in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-20-2016, 01:04 PM
  4. alternative to FOR EACH to apply COUNTIF formula to each cell to check for DUPLICATE
    By dwx in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-01-2014, 11:20 AM
  5. [SOLVED] Enter formula result from one cell into a COUNTIF formula in another cell. Help please!
    By gregrach in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-10-2014, 11:23 AM
  6. Countif multiple criteria and then check the result against a date (ageing)
    By Mad-Dog in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-02-2013, 05:13 AM
  7. Need formula to check adjacent cell value, and report.
    By theRDstore in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-05-2009, 01:52 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1