+ Reply to Thread
Results 1 to 3 of 3

Countif Function

  1. #1
    Registered User
    Join Date
    11-05-2007
    Posts
    82

    Countif Function

    Hello all,

    I have a spreadsheet that I am trying to create a number of formulas on:

    The spreadsheet contains columns of data, columns D, F, I, and L and within these columns are %'s.

    For each row (eg:2) - I want to count the instances that D2, F2, I2 and L2 are greater than 3%. The count if function works great when the Columns between are deleted - but cant figure out a way to look at seperate cells and count on each?

    eg: I am trying to do: =Countif(D2,F2,I2,L2,">3%") but doesn't work as its expecting the criteria once the comma is entered. Is there any way to do this as I need to flag up when more than 2 instances occur.

    Any ideas appreciated.

    Thanks

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420
    Hi,

    You could use SUMPRODUCT()

    In a spare row, say row 1, mark the columns you want to count with an "I" (for include), in this example D1, F1, H1 & L1. (If there had been an equal gap between the columns you could avoid even this step and use a MOD() function in the formula below - but no matter).

    Now use the formula:

    Please Login or Register  to view this content.
    HTH

  3. #3
    Registered User
    Join Date
    11-05-2007
    Posts
    82
    Excellent - Works a treat.

    Another formula that I am trying to do within the same sheet - is to identify if any of the selected cells (D2,F2,I2,L2) are within 0.1% of each other.

    So if D2: 0.15% , F2: 0.23% then flag
    of ir F2: 0.15% , L2: 0.12% then Flag

    There are also negative % within these cells.

    Any idea's?

+ 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. countif function
    By rubio in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 04-11-2008, 05:08 PM
  2. COUNTIF function using a drop down list source data as the criteria.
    By Stevie-B in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 03-07-2008, 08:22 AM
  3. Glitche in the Countif Function
    By calli in forum Excel - New Users/Basics
    Replies: 6
    Last Post: 12-07-2007, 05:13 PM
  4. COUNTIF Function using more than one criterion
    By ary in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-19-2007, 05:14 AM
  5. Nesting a countif function
    By kevinr1 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2007, 09:57 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