software

I need excel to count the number of rows which contain both of two
conditions in seperate cells. the condtions are a specified number AND
a specified letter, if the row only contains one of either conditions i
need excel to ignore it, but if it contains both then count it as one.
I have used the SUMPRODUCT function but it doesnt seem to work, im
guessing because one of the conditions is text not numerical

any ideas?
many thanks--
SamGB
------------------------------------------------------------------------
SamGB's Profile: www.excelforum.com/member.php...oamp;userid=31263
View this thread: www.excelforum.com/showthread...hreadid=510798Are these values in specific columns, or are you saying you want to test if
the value is in any cell in the row?

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

quot;SamGBquot; gt; wrote in message
...
gt;
gt; I need excel to count the number of rows which contain both of two
gt; conditions in seperate cells. the condtions are a specified number AND
gt; a specified letter, if the row only contains one of either conditions i
gt; need excel to ignore it, but if it contains both then count it as one.
gt; I have used the SUMPRODUCT function but it doesnt seem to work, im
gt; guessing because one of the conditions is text not numerical
gt;
gt; any ideas?
gt; many thanks
gt;
gt;
gt; --
gt; SamGB
gt; ------------------------------------------------------------------------
gt; SamGB's Profile:
www.excelforum.com/member.php...oamp;userid=31263
gt; View this thread: www.excelforum.com/showthread...hreadid=510798
gt;
Sam

a couple of ways:

=SUMPRODUCT((A2:A29=1)*(B2:B29=quot;aquot;))

=SUMPRODUCT(--(A2:A29=1),--(B2:B29=quot;aquot;))

Same effect, slightly different ways of doing it

Note that

=SUMPRODUCT((A2:A29=1),(B2:B29=quot;aquot;))

does *not* work

Regards

Trevorquot;SamGBquot; gt; wrote in message
...
gt;
gt; I need excel to count the number of rows which contain both of two
gt; conditions in seperate cells. the condtions are a specified number AND
gt; a specified letter, if the row only contains one of either conditions i
gt; need excel to ignore it, but if it contains both then count it as one.
gt; I have used the SUMPRODUCT function but it doesnt seem to work, im
gt; guessing because one of the conditions is text not numerical
gt;
gt; any ideas?
gt; many thanks
gt;
gt;
gt; --
gt; SamGB
gt; ------------------------------------------------------------------------
gt; SamGB's Profile:
gt; www.excelforum.com/member.php...oamp;userid=31263
gt; View this thread: www.excelforum.com/showthread...hreadid=510798
gt;