Nested if statatement + count

[Marvin85]

Hello friends,

I am new to nested if statement. I will like to see how many people in my data are ELIGIBLE for contraception services and what is the percentage of all my participants. In order for my participants to be ELIGIBLE, they have to be currently active, they can not be pregnant, and they can not be seeking pregnancy. My goal is to have a table in tab 2 that summarizes this info (# eligible and % of total). My idea was to create a new column (eligible) in my dataset with the nested if statements. Then in tab , I will count how many are eligible and generate the percentage. What will be a nice way to do this. I want to impress my supervisor.


Thank you in advance.
Marvin

[TMS]

U2 on data sheet:

=(I2="yes")*(J2="no")*(K2="no")

copied down.


and, in Sheet1:
B3:

COUNTIFS(Data!$I$2:$I$10,"yes", Data!$J$2:$J$10,"no",Data!$K$2:$K$10,"no")

C3:

=COUNTIFS(Data!$I$2:$I$10,"yes", Data!$J$2:$J$10,"no",Data!$K$2:$K$10,"no")/COUNTA(Data!$A$2:$A$10)

Regards, TMS

[gmr4evr1]

Not to step on toes, but an alternative to the formula in U2 would be

=IF((I2="yes")*(J2="no")*(K2="no"),"Yes","No")

This will give a "Yes" or "No" in the U column

[TMS]

No toes trodden on. It's always useful to have different viewpoints

If you put 1 or 0 in column U, you can just sum them. If you put "yes" or "no" in column U, you then have to use COUNTIF on the summary page.

Horses for courses. I didn't actually use column U on the Summary page so it really depends what the OP wants to see on the Data sheet. In which case, you don't need the extra column on the Data sheet at all.

@Marvin85: go impress your supervisor

[gmr4evr1]

After putting the =IF formula into U2, I used the formulas you provided for the Data sheet and it worked out great without changing anything.

[TMS]

[TMS]

Updated sample workbook ...

[gmr4evr1]

Now the OP has both options to choose from. I'm going to save your updated sample for the COUNTIFS, as I don't have a lot of experience with it.

[Marvin85]

Wow! Thanks guys.There are many options here. I will make sure that I understand and practices these formulas so I wont bother you so much in the future. Have a nice sunday!

[gmr4evr1]

You're welcome. It's no bother to help out. I/we no how frustrating it can be when you know what you want to do, but can't get anything you try to work correctly.

[TMS]

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

[Marvin85]

Will do TMS. One more thing. The formulas are perfect but the problems is that sometimes my grantees fill the dataset incorrectly. The main purspose if this dataset is to assess contraceptive services of health centers. So if a client is pregnant, we should not allow more entries in that line (for that client)- that row should be empty after the pregnancy field to ensure better data quality. The problems is that some grantees (staff) enter data by mistake and make the dataset messy. Is there a way to restrict entries in a cell based on the previous or other cells? Data validation? If i can do this my dataset will be much cleaner. Please let me know if you have any ideas.

Best,
MArvin

[Marvin85]

ANyone Please! or should I open a new discussion?