sumproduct formula

[Guest]

I get to a certain point and I just can't figure stuff out.

I have this formula:

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!H10:H13,1))))

This is to count cells that have data beginning with a number, however there
is one piece of text that I want included in the sum. "switch". How can I
get this text included in the sum?

Thank You
Brian

 

[Peo Sjoblom]

A couple of ways

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol
Log'!H10:H13,1))+ISNUMBER(SEARCH("switch",'Patrol Log'!H10:H13))>0))

if switch is always in one place, if it can be in several locations but you
only want to count those that start the string

=SUMPRODUCT(--(ISNUMBER(-LEFT('Patrol Log'!H10:H13,1))+(LEFT('Patrol
Log'!H10:H13,6)="switch")>0))

--
Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)