# COUNTIFS equivilant in Excel 2003

1. ## 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.

2. 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. 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?

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!

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

#### 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