+ Reply to Thread
Results 1 to 3 of 3

Countifs no returning the correct value

  1. #1
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Countifs no returning the correct value

    Hello!
    I am trying to use the formula below but it is not giving me the correct result

    =COUNTIF(Q5:Q44,"L")+COUNTIF(Q5:Q44,"NL")+COUNTIF(A5:A44,"EE")

    What I want is to count Q5:Q44 if it says NL and L but only if A5:A44 says EE. the problem is that the result should be 2 but is returning 8

    my columns so far look like this:
    A
    1 EE
    2 LL
    3 BP
    4 504
    5 EE

    Q
    1 L
    2 L
    3 NL
    4 NL
    5 NL

    THEREFORE IT SHOULD ONLY COUNT THE 1st AND 5th ROW WHICH ARE THE ONLY ONES THAT HAVE BOTH CONDITIONS THE "EE" AND THE "L" AND THE "EE" WITH THE "NL"

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: Countifs no returning the correct value

    hi there. your eg only has 5 entries. and your solution will give you 7 because COUNTIF(Q5:Q44,"L") is 2. COUNTIF(Q5:Q44,"NL") is COUNTIF(Q5:Q44,"NL")is 3 and COUNTIF(A5:A44,"EE") is 2. add them up and you get 7.

    try this instead:
    =SUM(COUNTIFS(Q5:Q44,{"L","NL"},A5:A44,"EE"))

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Contributor
    Join Date
    11-28-2011
    Location
    puerto rico
    MS-Off Ver
    Excel 2007
    Posts
    113

    Re: Countifs no returning the correct value

    Thank you so much!

+ 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. Countifs returning Zero
    By MikeSham in forum Excel General
    Replies: 5
    Last Post: 07-20-2016, 05:53 PM
  2. Replies: 4
    Last Post: 01-06-2016, 08:24 PM
  3. [SOLVED] CountIfs returning 0
    By jsmilke in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-19-2015, 04:59 PM
  4. COUNTIFs formula returning a #VALUE!
    By GanbareGoemon in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-27-2014, 10:46 AM
  5. [SOLVED] COUNTIFS returning #VALUE!
    By photoryan in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-18-2013, 01:07 AM
  6. [SOLVED] Unsure how to fill my Rows with correct COUNTIFS and vlookups
    By dmejia174 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-24-2013, 05:51 AM
  7. Formula sometimes returning correct answer and sometimes returning 0
    By redimp in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-09-2010, 06:28 AM

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