# 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.  Register To Reply

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  Register To Reply

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?  Register To Reply

4. 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!  Register To Reply