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.

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?

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.

Countif function | Microsoft Community Hub

20 Replies

IngeborgHawighorst
MVP
Sep 07, 2017
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.
- Angela Jenkins
  Copper Contributor
  Sep 17, 2017
  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.
  scout adventure sample.xlsx20 KB
  - IngeborgHawighorst
    MVP
    Sep 20, 2017
    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?

Forum Discussion

Countif function

20 Replies

Resources