+ Reply to Thread
Results 1 to 13 of 13

Formula to use to count the number of cells in a column which meet three sets of criteria

  1. #1
    Registered User
    Join Date
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Formula to use to count the number of cells in a column which meet three sets of criteria

    Hi,

    I'm looking for a formula which will count the number of times a value of less than 0 appears in column AT, but will only count this if the value 1 does not appear in the corresponding row in column E and column F. e.g.

    E F AT

    7 1 -4
    8 0
    9 -3
    10 1 -2
    11 -1

    In the above example, the calculation would exclude rows 7 and 10 as there is a "1" in column E or F. I would therefore count rows 9 & 11 with a value below 0 and the answer would be 2.

    Thanks

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

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    hi Dhabitude. try to upload a sample excel file next time as there the data is skewed when done here. maybe something like:
    =COUNTIFS(AT:AT,"<0",F:F,"<>1")

    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
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Hi Benishiryo,

    I tried the formula, but unfortunately it didn't work. How do I attach an excel file to this thread (sorry to ask what is probably obvious!)

    Many thanks,

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Maybe this...

    =COUNTIFS(AT7:AT11,"<0",E7:E11,"<>1",F7:F11,"<>1")
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  5. #5
    Registered User
    Join Date
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Hi Tony,

    Unfortunately this doesn't seem to work either. I only want it to count the negative values in column AT if there isn't a 1 in column E or in column F. Was wondering if I need an "AND" or "OR" somewhere in the countif formula.

  6. #6
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,598

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    The first part of my forum name is quite interested in this statement:

    "if there isn't a 1 in column E or in column F"

    Can you explain in other words what you mean by this logical construction?

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    I'll try! I have three columns of data. I want to count the number of times that a negative figure occurs in the third column (i.e countif(AT7:AT141<0), but I want the count to exclude any of the negative values if the number 1 appears in either of the first 2 columns.

    Therefore, if cell A1 was empty, Cell B1 was empty but Cell C1 had a negative value, the answer would be 1.
    If Cell A1 contained a 1, Cell B1 was empty but Cell C1 had a negative value, the answer would be 0 (as cell C1 would be ignored due to the 1 in Cell A1)
    If Cell A1 was empty, Cell B1 contained a 1 but Cell C1 had a negative value, the answer would be 0 (as cell C1 would be ignored due to the 1 in Cell A1)

    Many thanks

  8. #8
    Forum Guru XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,598

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    That's what I thought. But that's logically identical to saying:

    "Count the number of times that a negative figure occurs in the third column AND neither the first nor the second column contains a 1"

    i.e. Tony Valko's solution.

    Regards

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Can you post a SMALL sample file with about 20 rows worth of data and tell us what result you expect?

  10. #10
    Registered User
    Join Date
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Hi Tony,

    I realised when I went back to the sheet this morning that I was forgetting to reference the formula to the correct worksheet (as the summary was on a different sheet!). Felt such a fool and your formula now works perfectly thanks, so many many thanks for your help. I have a fairly similar query on the same piece of work and wondered if you may be able to help with that too. I'm counting the number of times particular text appears in a column, but as with the previous query, I want the count to ignore any rows where a 1 appears in column E or F of the sheet. I found a formula on line which counts the text successfully, but I'm not sure how to expand this so that it omits any rows with a 1 in column E or F. The formula I'm using is as follows: -

    =SUMPRODUCT((LEN(Employed!AU$7:AU$141)-LEN(SUBSTITUTE(Employed!AU$7:AU$141,"Sick","")))/LEN("Sick"))

    Many thanks,

  11. #11
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    Try this array formula**:

    =SUM(IF(Employed!E$7:E$141<>1,IF(Employed!F$7:F$141<>1,(LEN(Employed!AU$7:AU$141)-LEN(SUBSTITUTE(Employed!AU$7:AU$141,"Sick","")))/LEN("Sick"))))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.

  12. #12
    Registered User
    Join Date
    12-03-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2007
    Posts
    12

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    It works! You're a genius. With my humble knowledge of excel I don't even know what the formula means, but it works and I can now complete the piece of work I'm doing successfully.

    Thank you so much.

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to use to count the number of cells in a column which meet three sets of crite

    You're welcome. Thanks for the feedback!

+ 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] Count of rows in an array that meet criteria in column 2 and different criteria in column3
    By reynoldslarry in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 12-09-2013, 01:51 AM
  2. [SOLVED] Advanced Lookup to count cells in a column that meet criteria
    By shepherdc2814 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-14-2013, 12:59 PM
  3. [SOLVED] Trying to count the number of cells that meet two sets of Criteria
    By Pablo12 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 09-24-2013, 12:02 PM
  4. Replies: 5
    Last Post: 08-29-2011, 02:22 PM
  5. count non blank cells which meet criteria in another column
    By cmarsh5035 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-16-2005, 12:45 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1