COUNTIFS equivalent in 2003...?

[blist]

The following formula works as I'd like in Excel 2010.

=COUNTIFS(C:C,C2,K:K,"=J*")

What formula will return the same results when used in Excel 2003? I'm peddaling as fast as I can, and still unsuccessful after endless searching and testing for a solution. Huge thanks for any and every bit of help here!

 

[RSXchin]

You're looking for SUMPRODUCT

I'll try and find the correct usage for it.

 

[barry houdini]

You can't use whole column references for SUMPRODUCT in 2003......and you can't use wildcards so you need a version like this

=SUMPRODUCT((C$2:C$1000=C2)*(LEFT(K$2:K$1000)="J"))

adjust ranges as required

 

[RSXchin]

don't forget to hit CTRL+SHIFT+ENTER when you put that one in. Because it's an array formula (I think...)

 

[blist]

Thanks all, you helped me solve the issue!

Beers to you...

 

[Aladin Akyurek]

don't forget to hit CTRL+SHIFT+ENTER when you put that one in. Because it's an array formula (I think...)

It's true that it processes array objects and it is designed to do so per default that there is in fact no need for control+shift+enter.