=sumproduct(--(a2:a99=2),--(b2:b99=5))
will give the number of rows that have 2's in column A and at the same time 5's
in column B.

Adjust the ranges to match--but you can't use whole columns (except in xl2007+).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

============

If you're using xl2007, there's a new =countifs() function that will do the same
kind of thing.

=======

To determine the percentage, you can divide this total by the overall total (I
think that's what you want).

=Counta(a2:a99)
will return the number on non-empty cells in that range.

So maybe...

=sumproduct(--(a2:a99=2),--(b2:b99=5)) / counta(a2:a99)

is what you want (and format as a percentage).


alh06 wrote:

This is what I am trying to find:

Of the people in column 'A' who identified as a "2", what percentage also
identified as a "5" in column 'B'? --

I have no clue what formula to use - I tried using COUNTIF combined with IF
and thought it was right, but now certain numbers aren't working out
correctly. Then i thought maybe I needed to use AND as I'm trying to find two
things??

If anyone can give me a template using my information above, that would be
fantastic! Thank you!

--

Dave Peterson