Forum Discussion
Countif function
Hello,
it's probably best to see this in a sample file. Please mock up a few scenarios and the expected results. The final setup may require some helper columns where the parameters for a formula can be worked out with formulas. It may be a bit more complex than a Countifs function can handle.
Please reply and click Choose Files to upload a sample file.
- Hi, Ingeborg.I have attached samples of the two adventures I'm still having problems figuring out. If you can get those two areas to work, I think I should be able to fix the others that I'm having problems with.Thanks for any help you can provide.
Hello again,
looking at sheet 2 of your attached file, I see that you use row 12 to evaluate option A and row 21 to evaluate Option B. Each option has a different set of questions and you evaluate them separately.
The formula in row 22 for the overall evaluation does not look right, though. It should be
=IF(E12=" ",E21,E12)
I'm not qute sure I understand what the challenge is on Sheet 1. Is something not working there?
Looking at Sheet1 again:
how about you use cell D4 to figure out if step 2 has been achieved. If the scout needs to complete 4 out of 5 of the sub-tasks of task 2, then use
=IF(COUNTIF(D5:D9,"a")>=4,"a","p")
Similar in D12. If they need to achieve, say, 6 out of the 9 subtasks, use in D12:
=IF(COUNTIF(D13:D21,"a")>=6,"a","p")
Then, total the eight question results, but ignore the rows with the sub-tasks, because they have been rolled up into D4 and D12. So for "requirement 1 and 5 others" you could do
=IF(AND(D3="a",COUNTIF(D4,"a")+COUNTIF(D10:D12,"a")+COUNTIF(D22:D24,"a")>=5),"C","P")
Does that make sense?
