COUNTIFS alternative for older excel versions
 

The below formula works fine in excel 2007 but not 2003. Has anybody any idea
how I can get this to work in excel 2003.

1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3")
2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3")



f h
target current
5a 1
5a 1
5a 2
5a 3 1st Formula 32
5a 4 2nd Formula 21
5a 4
5a 3
5a 1
5a 2
5a 2
5a 2
5a 2
5a 2
5b 1
5b 2
5b 3
5b 4
5b 4
5b 2
5b 4
5b 2
5b 2
5b 1
5c 1
5c 2
5c 3
5c 3
5c 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 2
6a 3
6a 1
6b 4
6b 1
6b 2
6b 2
6b 3
6b 3
6b 3
6c 3
6c 2
6c 3
6c 2
6c 4
7a 3
7a 2
7b 1
7b 1
7b 4
7c 4
7c 2
7c 3
8a 4
8a 1
8b 2
8b 3
8c 1
8c 3

COUNTIFS alternative for older excel versions
 

Maybe this

=COUNT(IF(SEARCH("5?",A2:A64),B2:B64))

'This is an array formula which must be entered with CTRL+Shift+Enter and NOT
'just enter. If you do it correct then Excel will put curly brackets around
'the formula{}. You can't type these yourself. If you Edit the ranges
'then you must re-enter as An array


Mike

"Jon Mac" wrote:

The below formula works fine in excel 2007 but not 2003. Has anybody any idea
how I can get this to work in excel 2003.

1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3")
2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3")



f h
target current
5a 1
5a 1
5a 2
5a 3 1st Formula 32
5a 4 2nd Formula 21
5a 4
5a 3
5a 1
5a 2
5a 2
5a 2
5a 2
5a 2
5b 1
5b 2
5b 3
5b 4
5b 4
5b 2
5b 4
5b 2
5b 2
5b 1
5c 1
5c 2
5c 3
5c 3
5c 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 2
6a 3
6a 1
6b 4
6b 1
6b 2
6b 2
6b 3
6b 3
6b 3
6c 3
6c 2
6c 3
6c 2
6c 4
7a 3
7a 2
7b 1
7b 1
7b 4
7c 4
7c 2
7c 3
8a 4
8a 1
8b 2
8b 3
8c 1
8c 3

COUNTIFS alternative for older excel versions
 

Hi,

The answer depend on whether 5* represents numbers or text if numbers then

=SUMPRODUCT(--(F4:F66=50),--(H4:H66=3))

if text such as 5qwa then:

=SUMPRODUCT(--(LEFT(B4:B66)="5"),--(C4:C66=3))

If this helps, click the Yes button

Cheers,
Shane Devenshire

"Jon Mac" wrote:

The below formula works fine in excel 2007 but not 2003. Has anybody any idea
how I can get this to work in excel 2003.

1st Formula =COUNTIFS(F4:F66,"=5*",H4:H66,"=3")
2nd Formula =COUNTIFS(F5:F67,"=6*",H5:H67,"=3")



f h
target current
5a 1
5a 1
5a 2
5a 3 1st Formula 32
5a 4 2nd Formula 21
5a 4
5a 3
5a 1
5a 2
5a 2
5a 2
5a 2
5a 2
5b 1
5b 2
5b 3
5b 4
5b 4
5b 2
5b 4
5b 2
5b 2
5b 1
5c 1
5c 2
5c 3
5c 3
5c 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 3
6a 2
6a 3
6a 1
6b 4
6b 1
6b 2
6b 2
6b 3
6b 3
6b 3
6c 3
6c 2
6c 3
6c 2
6c 4
7a 3
7a 2
7b 1
7b 1
7b 4
7c 4
7c 2
7c 3
8a 4
8a 1
8b 2
8b 3
8c 1
8c 3