A double =COUNTIF (for example of all the A's how many have a "yes"?)

[Eidius]

basically what the title said.
if I have a data sheet that I have about 300 numbers and each of those are split into 5 categories and of those 5 categories each one has either a NO or a YES assigned to it.

how can I command excel to count the number of Yes entry in a the "A" category.

note: my data is set as random, this means in column 1 I have my entire sample with each row, randomly, categorizing itself as a A to E and in column 2 I have the YES or NO data.
this means that excel has to go through each of my rows and find which row has an "A" and of this "A" how many have a YES inside the same row under the second column.

I hope I was clear.
Thank you for your time.

[Søren Larsen]

Try this:

Please Login or Register  to view this content.

Be sure to adjust the ranges to match your data.

[oeldere]

Or you use pivot table for that.

[Eidius]

it did not work, it gave me a zero.

here is what I wrote:
=SUMPRODUCT((B2:B343="1")*(C2:C343="y"))

the B colum from 2 to 343 are the five categories, they are not labeled as A but 1 to 5, I only called it that for an easier explantion.

the column and row numbers are correct, I assure you.

edit: I am not sure what a pivote table is, Maybe i should have payed attention in Excel class... but then again we did simple stuff I already know :P

[martindwilson]

did you try
=SUMPRODUCT((B2:B343=1)*(C2:C343="y"))

[Eidius]

yes and it gives me #NAME?

previously I used =COUNTIF(B2:B343,"1") and it worked fine, its the second element thats giving me issues...

[martindwilson]

???that does not give #name?

[Eidius]

???that does not give #name?

i just edited my post, please read the message again for new info and yes I entered this:
SUMPRODUCT((B2:B343=1)*(C2:C343=y))

and it does give me a error read as "#NAME?"

[oeldere]

SUMPRODUCT((B2:B343=1)*(C2:C343="y"))

You have to take the right decription.

See the diverance with you formula in #8.

[Eidius]

oh it works now, I forgot to put the y between " ", it now works normally and indicates there are 32 Ys in my 64 total of 1s.

thank you, I also asked a question regarding a box plot. here is the link, if you can help me with this it will totally make my day.
http://www.excelforum.com/excel-gene...ease-help.html

thank you again.