Look Who it Was Random video

Excel - Troubleshooting the COUNT Function in Excel | MrExcel Netcast - Episode 534

477 views · Published 4 August 2009 · 2:24 · Indexed 20 September 2026

Channel: MrExcel.com · 2009 · Education

Watch on YouTube

Microsoft Excel Tutorial - Troubleshooting the COUNT Function in Excel | MrExcel Netcast.

Welcome back to another episode of the MrExcel netcast! In today's video, we will be discussing a question sent in by Kyle from California. If you have a question for the podcast, be sure to send it in and we will try to feature it in a future episode.

Kyle was setting up a workbook for his students to use when practicing for the ACT or SAT tests. He had a column for students to enter their answer and a hidden column with the correct answer. To mark if the student got the answer right, Kyle used a simple IF statement. However, when he tried to use the COUNT function to count the number of correct answers, he ran into some issues.

The first problem was that the COUNT function only counts numeric values, not text values. So, using the COUNTA function to count both numeric and text values didn't work either because the blank cells were also counted. That's when I suggested using the COUNTIF function. This function allows us to specify a range and count how many cells meet a certain criteria. In this case, we can use =COUNTIF(D2:D11,"X") to count the number of Xs in the range.

Another solution I suggested was to use a 1 instead of an X and a blank. This way, we can use the SUM function to add up all the 1s and get the total score. This method may not be visually appealing, but it gets the job done. So, if you're just looking for the total score and don't care about presentation, using 1s is the way to go.

In conclusion, the COUNTIF function is a great tool for counting specific values in a range. It's especially useful when dealing with text values. And if you're just looking for the total score, using 1s instead of Xs and blanks can make the calculation easier. Thank you, Kyle, for sending in your question. If you have a question for us, don't hesitate to reach out. And be sure to tune in next time for more helpful tips and tricks from MrExcel.

Buy Bill Jelen's latest Excel book: https://www.mrexcel.com/products/latest/

You can help my channel by clicking Like or commenting below: https://www.mrexcel.com/like-mrexcel-on-youtube/


Kyle is trying to build a worksheet to create practice SAT tests for his students. His IF formula to mark answers as correct is working fine, but the COUNT function cant seem to count the correct answers. Episode 534 troubleshoots this function.

This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!

Table of Contents:
(00:00) Introduction and question from Kyle
(00:23) Kyle's setup for practice tests
(00:36) Formula for marking correct answers
(00:51) Issues with using COUNT and COUNTA functions
(01:12) Solution using COUNTIF function
(01:32) Alternative solution using SUM function
(02:06) Clicking Like really helps the algorithm

#excel #microsoft #microsoftexcel #exceltutorial #exceltips #exceltricks #excelmvp #freeclass #freecourse #freeclasses #excelclasses #microsoftmvp #walkthrough #evergreen #spreadsheetskills #analytics #analysis #dataanalysis #dataanalytics #mrexcel #spreadsheets #spreadsheet #excelhelp #accounting #tutorial 

This video answers these common search terms:
COUNT function
COUNTA function
COUNTIF function
hidden answer
if statement
practice ACT test
presentation
SAT test
SUM function
total score
workbook setup
YouTube video search terms: MrExcel netcast

Join the MrExcel Message Board discussion about this video at https://www.mrexcel.com/board/threads/1152554/

More from this channel