+ Reply to Thread
Results 1 to 2 of 2

Alternative to standart countifs function needed

  1. #1
    Registered User
    Join Date
    12-19-2014
    Location
    london
    MS-Off Ver
    2010 excel
    Posts
    1

    Alternative to standart countifs function needed

    Here is a story... columns A,B,C,D,E is a list,(can be formated as a table) and contain variables in form of numbers.These numbers are from 0 to 37. Need to count how many times each individual number in column E corresponds to the given sett of criteria (columns A,B,C,D). Problem is that l need to account not for individual sets of these criteria, but for groops of them. Groops are to be specified in other worksheet. I can not use ">" , "<" as a criteria argument due to the nature of variables.
    If COUNTIFS function would accept any one of values in specified range of sells as valid criteria, it would look like this
    =countifs('Sheet1'!A:A,A1:A18,'Sheet1'!B:B,B1:B18...)
    Any ideas what could be used?

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

    Re: Alternative to standart countifs function needed

    Maybe something like this...

    =SUMPRODUCT(--ISNUMBER(MATCH('Sheet1'!A1:A100,A1:A18,0)),ISNUMBER(MATCH('Sheet1'!B1:B100,B1:B18,0)))

    This means...

    Count if Sheet 1 col A = or A1:A18 AND Sheet1 col B = or B1:B18

    You should avoid using entire columns as range references in array formulas and the SUMPRODUCT function. Use smaller specific ranges.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ 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. Alternative Formula of Countifs
    By vkey in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-28-2012, 03:49 AM
  2. Excel 2007 : Help needed with COUNTIFS function
    By milliemoo in forum Excel General
    Replies: 18
    Last Post: 10-25-2011, 03:44 PM
  3. countifs alternative
    By adam2308 in forum Excel General
    Replies: 1
    Last Post: 06-23-2011, 05:20 AM
  4. COUNTIFS alternative
    By markrennolds in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-28-2010, 03:55 PM
  5. Replies: 6
    Last Post: 04-21-2010, 02:53 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