Countif's and And functions

D

Danielle

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.
 
D

Danielle

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 said:
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?
Click to expand...
 
K

Kevin Lehrbass

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,
 
Top