Complex Count query I think?!

[Shingaru]

a 1 2 0 4 5
b 1 2 3 0 5
a 0 0 3 4 5
b 1 2 3 4 0
a 1 2 0 0 0


what I am trying to achieve is, if column A = "a" then count the number of items that do not equal 0, in this case the answer should be 9 (4 from first line, 3 from 3rd, and 2 from the 5th line)

driving me mad im sure its something simple.

Many thanks

[ConneXionLost]

How about:

Please Login or Register  to view this content.

Cheers,

[Shingaru]

worked like a charm, thanks

[Xx7]

Macro solution:

Please Login or Register  to view this content.

[Shingaru]

i have been trying to enhance the formula to take a wildcard. i.e. if 'a' appears in the cell count it but it doesnt like it any suggestions?

abc 1 2 0 4 5
b 1 2 3 0 5
afgd 0 0 3 4 5
b 1 2 3 4 0
aqw 1 2 0 0 0

in this case i want to search for where a appears?

=SUMPRODUCT((A1:A5="*a*")*(B1:F5<>0))

i tried this but it returns 0. in the case above i would want it to return the same answer 9

[Shingaru]

=SUMPRODUCT(ISNUMBER(FIND("a",A1:A5))*(B1:F5<>0))

solved it :D thanks

[newdoverman]

The only thing that I could get to work was too late