Results 1 to 3 of 3

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

Threaded View

RandomRed *COUNTIF with nested AND/OR... 01-30-2014, 04:57 PM
benishiryo Re: *COUNTIF with nested... 01-30-2014, 09:32 PM
RandomRed Re: *COUNTIF with nested... 01-31-2014, 10:26 AM
  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

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