countif using vba

[biggercrap]

How am I going to count column 'A' contain # and column 'E' contain the word AT using VBA?

e.g Below occurence is 2 (highlight in red)

A B C D E
1 1x AT
2 2
3 3# AT
4 4a
5 5i AT
6 6 AT
7 7 AT
8 8# AT
9 9#
10

 

[RoryA]

Which version of Excel?

 

[biggercrap]

2003

 

[RoryA]

You could use:

Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""#"",A1:A10)),--(E1:E10=""AT""))")

if you really need VBA.

 

[biggercrap]

You could use:

Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""#"",A1:A10)),--(E1:E10=""AT""))")

if you really need VBA.

Hi,
There is still error. Why do we need to use evaluate?

 

[biggercrap]

Hi,
There is still error. Why do we need to use evaluate?

what about if the to count column a without '#' ?

 

[GTO]

Hi,
There is still error. Why do we need to use evaluate?

Hi there,

I believe just a minor typo/one missing parenthesis:

Evaluate("SUMPRODUCT(--(ISNUMBER(FIND(""#"",A1:A10))),--(E1:E10=""AT""))")