Countif function

[Angela Jenkins]

Please help! I'm working on updating an advancement tracker for cub scouts. The worksheet uses the COUNTIF function to count the number of cells that have an A to determine whether to mark the adventure C for complete or P for partial. A simple example: Cast Iron Chef has three requirements but only two need to be completed for the adventure to be considered complete. The formula looks like this: =IF(COUNTIF(E8:E10,"A")>1,"C",IF(COUNTIF(E8:E10,"A")>0,"P"," ")). This formula works great for simple adventures. My problem is with the adventures that have more steps to complete and those steps have sub-steps. How can I create a formula for adventures that have a set number of requirements to complete but depending on which requirements the scout chooses to complete, the total number of A's will vary? Example: First Responder has 8 requirements. The Scout must complete Requirement 1 and at least 5 others. However, Requirement 2 has five sub-steps and Requirement 5 has nine sub-steps. I can't use a simple COUNTIF and count five A's because of the extra steps to complete for Requirements 2 & 5, and I haven't figured out how to rewrite the formula to account for the extra steps. I have Office 365.

[#2]

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.

[#3]

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.

[#4]

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?

[#5]

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?

[#6]

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.

[#7]

My mistake, I meant that Cindy would have a total of 5 letters. Sorry for the typo

[#8]

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.

[#9]

Okay, i see

[#10]

As a variant you could use in D2

=SUMIF($A$2:$A$70,A2,$C$2:$C$70)+COUNTIFS($A$2:$A$70,A2,$B$2:$B$70,"Y")

and drag down then

[#11]

thank you I started a new conversation as you requested.

[#12]

Thank you. I didn't find it so far, but if appears i'll repeat an answer

[#13]

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.

[#14]

Since the main questions ID:s (column A) are numbers (e.g.7) and subquestions ID:s are text (e.g. 7a, 7b) we may use to calculate number of full answers

=SUMPRODUCT((ISNUMBER($A$3:$A$16)*(UPPER($D$3:$D$16)="A")))

and add IF logic on the top

[#15]

Oops, sorry, that was question to Ingeborg. Two separate question are discussing in the same thread...

[#16]

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.

[#17]

@raquel.howser please don't hijack another member's question, and @Sergei Baklan, it would be less confusing if you could take the hijack to a different thread, so we can concentrate on @Angela Jenkins question.

[#18]

Ingeborg,

That works great, but I would like the bottom row to show a blank if none of the requirements have been completed. How would you write the formula in D17 to accomodate that? I would like this to show C if all requirements have been met, P if only some of the requirements have been met, or a blank if none of them have been completed. I have been able to modify your other suggestions to accomplish that but this one is escaping me.

[#19]

You could use an IF statement and check the count for zero, then check again for markings.

=IF(COUNTIF(D3:D9,"a")+COUNTIF(D13:D14,"a")=0,"",if(COUNTIF(D3:D9,"a")+COUNTIF(D13:D14,"a")>=6,"A","P"))

[#20]

Ingeborg,

Double-checking these to make sure as many scenarios are accounted for as possible, it doesn't look like Sheet 2 is calculating correctly. D22 is displaying P when D12 is blank AND D21 is C, instead of displaying C to reflect that Option B has been completed. I have uploaded a new sample copy and marked it with comments to help you understand what I'm seeing.

I'm not very good with formulas, so I really appreciate all your help on these.