Forum Discussion
Countif function
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.
Looking at cell D9 for question 7. Your formula is unneccesarily complicated.
=IF(COUNTIF(D10:D13,"A")>1,"A", IF(COUNTIF(D10:D13,"A")>0,"P"," "))
It can be reduced to
=IF(COUNTIF(D10:D13,"A")>1,"A","P")
So, if the count is greater than 1, it's achieved, else it is not.
Similarly for cell D14, where you want to check if there is one out of two. You only need
=IF(COUNTIF(D15:D16,"A")>0,"A","P")
If the scout needs to achieve 6 out of these 9 tasks, then the formula in D17 can be like this:
=IF(COUNTIF(D3:D9,"a")+COUNTIF(D13:D14,"a")>=6,"A","P")
D3 to D9 contain the answers to questions 1 to 7, D13 and D14 contain the answers to questions 8 and 9. Count the "A"s in these cells and check if it's 6 or greater.
Good Morning-
I have a question on how to combine the COUNTIF Function along with the SUMIFS function. I would like to take the total of approval letters which would be the "Y"s and add it to the sum of Denial letters for the nurse Cindy. Therefore, Cindy sent 1 approval letter and 3 denial letters which would be a total of 4 letters. How would I write that in a formula?
I apologize in advance If i posted this message in the wrong place or used the wrong procedure, I am new to this procedure.
Hi Raquel,
Yes, that's a separate question, please start in new thread - go to Formulas and Functions here and click Start new conversaton button.
And please clarify for you question - you have few records for each nurse (e.g. for Cindy 1 approval + 3 denial; another record for her no approval + 1 denial). Would you like to calculate sum of approvals for all records or for each record separatelly.
In first case for Cindy it'll be 1+3+0+1 = 5 total letters.
