OverviewStep 1 creating a seriesStep 2 adjust row heightsStep 3 add column totalsStep 4 add a names columnStep 5 add column headersStep 6 add currency formattingStep 7 freeze the column headingsStep 8 add a Salary columnStep 9 add a conditional formulaStep 10 add a COUNTIF formulaStep 11 utilising binary cellsStep 12 applying Conditional FormattingStep 13 adding text cellsStep 14 adding an AVERAGEStep 15 adding calculationsStep 16 adding a FilterStep 17 add a Pie Chart graphicStep 18 using the IF and CONCAT functionsADDITIONAL Work with timesADDITIONAL PrintingAdvanced Topic VLOOKUPsAdvanced Topics Pivot Tables
Excel training
First written: Feb-2024
last changed Mar-2024

Adding a COUNTIF formula

Here we want to add a formula to count how many cells are set to 1 and place this information into the total row cell E22

  1. As before, we could simply type in the formula into cell E22 but this time we will use an assistant to see how this works. Click on E22 and then click on the fx button to the left of the Formula Bar input field at the top of the worksheet

  2. This will show an Insert Function popup

  3. Type "COUNT" into the Search field, and click [Go]

  4. Select COUNTIF from the list of found functions in the list, then click [OK]

  5. Click on the Range input field

  6. Then using the mouse left-button, click and hold on cell E21 and drag up to E2 (tip, you may need/want to move the popup out the way while doing this)

  7. You should now see Range set to E2:E21 on the popup field

  8. Enter 1 into the Criteria field

  9. Click [OK]; and you should now see 11 in E22 and the Formula Bar reads =COUNTIF(E2:E21,1)

Advanced Note

You can also use the COUNTIFS function to apply multiple conditions, e.g. =COUNTIFS(E2:E21,1,B2:B21,"Alex") (resulting in 1 in this case); COUNTIFS can take any number of range,value pairs