Help w/ Formula!

[alh06]

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]

=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 here:
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).

 

[MarkN]

Hello,

=SUMPRODUCT(--(A2:A9=2),--(B2:B9=5))/COUNT(A2:A9)

will return the percentage of the total number in col a with values of 2 who
also have the value 5 on the corresponding row of col b.

Is that what you're after?

 

[alh06]

Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF
because I'm only looking to find the percentage of the number of "2"s that
answered, but I could change that on my own.

Now I have another question ... when I'm looking at people who identified as
a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100).
How do I put that into the equation ... I tried to just write
=SUMPRODUCT(--(V4:W100=2),--(E4:E100=5))

but it said the Value was wrong ... so I tried to put V4:V100, W4:W100,
X4:X100 and that didn't work either. I'm guessing I have to put them each in
on their own, but I don't know entirely where they go in regard to
parenthesis and funny dashes.

Thank you so much!!
Amber

 

[alh06]

Yes, that IS what I'm looking for!! I had to change the COUNT part to COUNTIF
because I'm only looking to find the percentage of the number of "2"s that
answered, but I could change that on my own.

Now I have another question ... when I'm looking at people who identified as
a '2' -- I'm actually looking at 3 columns for those numbers (range 4:100).
How do I put that into the equation ... I tried to just write
=SUMPRODUCT(--(V4:W100=2),--(E4:E100=5))

but it said the Value was wrong ... so I tried to put V4:V100, W4:W100,
X4:X100 and that didn't work either. I'm guessing I have to put them each in
on their own, but I don't know entirely where they go in regard to
parenthesis and funny dashes.

Thank you so much!!
Amber

 

[Dave Peterson]

This will count the number of cells in V4:W4 (both columns) that have 2's in
them while column E has a 5:
=SUMPRODUCT((V4:W100=2)*(E4:E100=5))

This will count the number of rows that match:
=SUMPRODUCT(--(E4:E100=5),--(((V4:V100=2)+(W4:W100)=2)>0))

 

[alh06]

Yea! Both formulas worked ... was that supposed to happen? When I read your
explanation of each, I'm not sure what the difference is. Trying them both,
they gave the same answer ... so I just want to be sure I'm using the correct
one in case there is a difference I'm unaware of.

Thank you soo much Dave! This has been driving me crazy the entire week at
work!

Amber