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.
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?
Yes, thanks, that made sense.
I've been going through everything to make sure the calculations are working properly and have found one more section that's giving me fits. It's another one of the electives that gives the scout a choice of requirements and two of those are multi-step requirements. He has to choose six out of nine requirements, but depending on which six he chooses he may have anywhere from six to 12 boxes checked. Can you help me with this one, please? It's on page 3 of the attached sample file.