Sumproduct if column begins with text
 

Hi. I've searched for half an hour to see if this question has already
been answered, and I'm not having any luck.

I have a detail sheet with multiple fields. I need to count when one
field begins with a Q and when another field equals "Y". When I know
the exact values I need (rather than a 'begins with') I can easily use
sumproduct. This one is tripping me up, though.

For example:

A1 B1
Q.501.1 Y
E.504.3 N
Q.505.4 Y

It should come up with two for the first and third rows of data in this
case. Make sense? Any help would be appreciated.


--
webee33
------------------------------------------------------------------------
webee33's Profile: http://www.excelforum.com/member.php...o&userid=28171
View this thread: http://www.excelforum.com/showthread...hreadid=479235

Sumproduct if column begins with text
 

=SUMPRODUCT(--(LEFT(A1:A3,1)="Q"),--(LEFT(B1:B3,1)="Y"))

should do the trick


--
bill k


------------------------------------------------------------------------
bill k's Profile: http://www.excelforum.com/member.php...nfo&userid=821
View this thread: http://www.excelforum.com/showthread...hreadid=479235

Sumproduct if column begins with text
 

=SUMPRODUCT(--(LEFT(A2:A200,1)="Q"),--(B2:B200="Y"))


--

HTH

RP
(remove nothere from the email address if mailing direct)


"webee33" wrote in
message ...

Hi. I've searched for half an hour to see if this question has already
been answered, and I'm not having any luck.

I have a detail sheet with multiple fields. I need to count when one
field begins with a Q and when another field equals "Y". When I know
the exact values I need (rather than a 'begins with') I can easily use
sumproduct. This one is tripping me up, though.

For example:

A1 B1
Q.501.1 Y
E.504.3 N
Q.505.4 Y

It should come up with two for the first and third rows of data in this
case. Make sense? Any help would be appreciated.


--
webee33
------------------------------------------------------------------------
webee33's Profile:
http://www.excelforum.com/member.php...o&userid=28171
View this thread: http://www.excelforum.com/showthread...hreadid=479235