# Times Name Appears given a condition in a table

**BlueValentine** · 03-20-2013, 05:51 PM

Please help!

In the attached table, I need to count the # of times a person has a condition with a "No". For example, Courtney has 2 reviews (row 2 and row 6) but only 1 review meets the condition of any "No" response to the review criteria (columns E-N). It doesn't matter how many "No" responses are present on a given row, only that at least 1 "No" is present. Then I need to count how many rows have a "No" condition for each person. So the answer for Courtney would be 1. I have tried many CountIF, IF, IFAND statements and I cannot get this to count what I need. Please help!Sample.xlsx

**swhitesell** · 03-20-2013, 06:35 PM

I solved your problem by placing an OR function in the last column of the spreadsheet. I then inserted a new sheet with the list of names with reviews and placed a COUNTIFS function in it to find where the OR statement was true for that name.

Sample(Review).xlsx

**BlueValentine** · 03-20-2013, 06:55 PM

I can't believe I didn't think of that! Thank you VERY much!!!

**FDibbins** · 03-20-2013, 07:12 PM

Hi and welcome to the forum

If you use swhitsell's suggestion, male sure you absolute his formula on sheet2 B2, else the references will change when copied...
=COUNTIFS(Report!O2:O6,Overview!A2,Report!Q2:Q6,TRUE)

An alternative...
sheet1 col R (or Q) use this, copied down...
=IF(COUNTIF(E2:N2,"No")>0,1,0)
then use this in the next column, copied down...
=COUNTIFS($O$2:O2,O2,$R$2:R2,1)

**BlueValentine** · 03-20-2013, 07:28 PM

I did catch that and made the adjustment. I will try yours as well, I have a much larger set of data to work with and yours may be a good alternative.
Thank you!!