Countif's and And functions

I need a formula that will take:

Countif(a2:a7="EA" AND b2:B7="set")

Obviously this isn't a valid formula but trying to accomplish this.

Found my answer is another persons question/reply:

=SUMPRODUCT(--(A2:A100="S"),--(B2:B100="E"))

so the answer is SP :-)

--

HTH

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


"JoatNIC" <JoatNIC@discussions.microsoft.com> wrote in message
news:622CFA2D-A1C4-4B72-9209-0ABB794A1927@microsoft.com...
> I need to count the number of cells in Column A that contain an S, AND the
> cells in Column B that contain an E. So, if there are a total of 6 rows,
> and 4 contain an S and 3 Contain an E the answer should be 3.
>
> Do I use COUNTIF OR SUMPRODUCT?
>
> What will the formula/function look like?



"Danielle" wrote:

> I need a formula that will take:
>
> Countif(a2:a7="EA" AND b2:B7="set")
>
> Obviously this isn't a valid formula but trying to accomplish this.

Hi Danielle,

What you need is a sumproduct formula. A sumproduct formula accepts various
criteria and gives you a count of the number of matches in your list.

Try using =SUMPRODUCT((A2:A7="EA")*(B2:B7="set")*(C2:C7))
where C2:C7 contains a 1 in each cell.

So, excel looks for an "EA" in cells A2:A7, and "set" in B2:B7 and then sums
the 1s in C2:C7.

You will love using Sumproduct !!! Try using a search engine to get more
examples of sumproduct for even more examples.

Let me know if you have any more questions (in the discussion group or send
me an email)

Cheers,
--
Kevin Lehrbass
www.spreadsheetsolutions4u.com


"Danielle" wrote:

> I need a formula that will take:
>
> Countif(a2:a7="EA" AND b2:B7="set")
>
> Obviously this isn't a valid formula but trying to accomplish this.