COUNTIFS alternative for older excel versions

[Jon Mac]

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

 

[Mike H]

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

 

[Shane Devenshire]

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