+ Reply to Thread
Results 1 to 3 of 3

*COUNTIF with nested AND/OR -- not consistently working

  1. #1
    Registered User
    Join Date
    01-30-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2013, as of 2/2015
    Posts
    11

    *COUNTIF with nested AND/OR -- not consistently working

    I wrote a formula to compare the individual values of cells B3:N3 against a fixed criteria of 2 and then against the individual values of cells B4:N4, if it's greater or equal to either, it should be counted.

    =COUNTIF(B3,">=2")+AND(OR(B3>=B4,1,0))*COUNTIF(C3,">=2")+AND(OR(C3>=C4,1,0))
    *COUNTIF(D3,">=2")+AND(OR(D3>=D4,1,0))*COUNTIF(E3,">=2")+AND(OR(E3>=E4,1,0))
    *COUNTIF(F3,">=2")+AND(OR(F3>=F4,1,0))*COUNTIF(G3,">=2")+AND(OR(G3>=G4,1,0))
    *COUNTIF(H3,">=2")+AND(OR(H3>=H4,1,0))*COUNTIF(I3,">=2")+AND(OR(I3>=I4,1,0))
    *COUNTIF(J3,">=2")+AND(OR(J3>=J4,1,0))*COUNTIF(K3,">=2")+AND(OR(K3>=K4,1,0))
    *COUNTIF(L3,">=2")+AND(OR(L3>=L4,1,0))*COUNTIF(M3,">=2")+AND(OR(M3>=M4,1,0))
    *COUNTIF(N3,">=2")+AND(OR(N3>=N4,1,0))


    I thought it was working, but when the next cell's grouping of data imported, it no longer returned the correct sum.


    2.07 1.68 1.40 1.77 1.22
    3.00 2.00 1.60 1.30 1.20

    # of weeks standard met 2 out of 5


    weeks 1,4 and 5 should have met the criteria, but currently only reflects 2 ...

    Thank you in advance for the much needed assistance!
    Last edited by RandomRed; 01-30-2014 at 05:04 PM. Reason: formatting

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

    Re: *COUNTIF with nested AND/OR -- not consistently working

    welcome to the forum.
    =SUMPRODUCT((((B3:N3>=2)+(B3:N3>=B4:N4))>0)*(B3:N3<>""))

    for alignment issues, you can (or should) always upload a sample excel file instead & also manually key in the results for a clearer picture. To upload, Click on GO ADVANCED and use the paperclip icon to open the upload window.
    View Pic
    and to illustrate your example better, click on How To Get Quick & Good Answers

    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
    Registered User
    Join Date
    01-30-2014
    Location
    Chicago
    MS-Off Ver
    Excel 2013, as of 2/2015
    Posts
    11

    Arrow Re: *COUNTIF with nested AND/OR -- not consistently working

    benishiryo -- thank you, I very much appreciate the much needed help! I've attached the example, I'm sorry -- I couldn't figure out how to attach in my original post, I barely figured out how to post... and showed what a forum novice I truly am

    P.S. when I used =SUMPRODUCT((((B3:N3>=2)+(B3:N3>=B4:N4))>0)*(B3:N3<>"")) -- it returned the value as 11 out of 5. I chalk this up to not providing the attachment and improperly formatting my inquiry. thank you again for the help!
    Attached Files Attached Files
    Last edited by RandomRed; 01-31-2014 at 12:25 PM. Reason: outcome of possible solution

+ 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. [SOLVED] VLookup not working consistently
    By pjw23 in forum Excel General
    Replies: 2
    Last Post: 04-27-2012, 03:45 PM
  2. 24 hour time not working in formula consistently
    By snake in forum Excel General
    Replies: 1
    Last Post: 09-15-2011, 12:52 PM
  3. nested countif not working
    By TedH in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2008, 11:21 AM
  4. Countif with nested function not working?
    By jshuatree in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-17-2006, 05:55 PM
  5. Conditional Formatting Not Working Consistently
    By Christina in forum Excel General
    Replies: 6
    Last Post: 07-22-2005, 07:05 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