+ Reply to Thread
Results 1 to 2 of 2

Ignore all negative values when counting in another column.

  1. #1
    Registered User
    Join Date
    03-13-2010
    Location
    Cape Town, SA
    MS-Off Ver
    MS Office 2013
    Posts
    59

    Ignore all negative values when counting in another column.

    I need to look at the $C$2:$C$21 and if any value is Negative or <0, then I do not want to "COUNTIF($D$2:$D$21,"A")"
    want to add all the different "Job Grades" for that period in the "Total No. of Jobs" blocks.

    I want to show the cancelled orders as well, so simply deleting that order is not my best option.

    What would the formula in cell D26 be?
    Attached Files Attached Files
    Last edited by Quagga; 03-18-2011 at 05:01 AM. Reason: Solved

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,087

    Re: Ignore all negative values when counting in another column.

    One way:

    =SUMPRODUCT(--($C$2:$C$21>0),--($D$2:$D$21=RIGHT(B26,1)))

    and drag down

    But,with Excel 2007, you can use COUNTIFS

    =COUNTIFS($C$2:$C$21,">0",$D$2:$D$21,RIGHT(B26,1))


    Regards
    Last edited by TMS; 03-18-2011 at 04:41 AM.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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