+ Reply to Thread
Results 1 to 4 of 4

COUNTIFS equivilant in Excel 2003

  1. #1
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153

    COUNTIFS equivilant in Excel 2003

    I have this function in Excel 2007:

    =SUM(IF(B1:B6="no",0,1/COUNTIFS(A1:A6,A1:A6,B1:B6,"yes")))

    *array formula*

    Here is what is located in A1:B6..

    2002 yes
    NH10 no
    NH10 yes
    NH10 yes
    FR54 yes
    FR54 no

    Basically I need a unique count of all items in column A where the value in colun B is equal to yes. Thanks!

    COUNTIFS, as you are aware, is specific to Excel 2007. Can you think of a way to redesign this formula so that it works in Excel 2003? I have tried a variety of SUMPRODUCT formulas but I haven't been able to reproduce the answer I need.
    Ecce Potestas Casei
    Nathan Head

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    You can use this formula

    =SUM(IF(FREQUENCY(IF(B1:B6="yes",IF(A1:A6<>"",MATCH(A1:A6,A1:A6,0))),ROW(A1:A6)-ROW(A1)+1),1))

    confirm with CTRL+SHIFT+ENTER

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902
    Does this work?
    Please Login or Register  to view this content.
    although I am not sure why your comparing A1:A6 to A1:A6... those will always yield TRUE arrays...no?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Forum Contributor Lotus123's Avatar
    Join Date
    11-07-2005
    Location
    Texas
    MS-Off Ver
    2007
    Posts
    153
    Quote Originally Posted by daddylonglegs
    You can use this formula

    =SUM(IF(FREQUENCY(IF(B1:B6="yes",IF(A1:A6<>"",MATCH(A1:A6,A1:A6,0))),ROW(A1:A6)-ROW(A1)+1),1))

    confirm with CTRL+SHIFT+ENTER
    Perfect - Thanks!

+ 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