How To Get an Item Count in Google Sheets

Here's a quick and dirty formula you can use in Google Sheets to get a running count of cells that contain any value (numbers, dates, text, links, etc.).

As an example, we'll create a little ersatz gradebook that will track the number of assignments that have been completed by a given student, then use that information to calculate a running average score (in other words, an average for only the assignments that have been completed).

This gradebook is, of course, for illustrative purposes only. This formula can be used to count anything in a spreadsheet over any range. It's also applicable across platforms and could easily be used in desktop tools like LibreOffice or Microsoft Excel.

 item count example in google sheets

The figure above shows our sample spreadsheet. In Column F, as you can see, we're counting the number of assignments completed using this formula:

=countif(B2:E2, "<>")

That simply creates a count of non-empty cells within a given range — in this case B2 to E2, in the next row down B3 to E3, in the one after that B4 to E4, etc. Those totals will automatically update any time a new score is added, as seen in the image below.

 sum example in google sheets

 

Time-saving tip: Once you insert the formula, you can select the cell, copy it, and paste to to a new row, an the cell range will automatically shift to the correct row. If instead you want to keep the original range, then double-click the cell and copy the text out of it, then paste it into a new cell.

The rest of it is very straightforward, but I'll explain it for those who might not be familiar with equations in spreadsheets.

In column G, "Total Points," we're simply using the SUM function and specifying the range of cells whose values we want to add together. That is illustrated in the figure below.

 divide example in google sheets

And then, finally, we're dividing the accumulated points (column G) by the total number of completed assignments (column F) in order to calculate a running average. This is done using the DIVIDE function, pictured below.

 item count example in google sheets

That's it! If you run into any trouble, ask me a question in the comments section below.

 

About the Author

David Nagel is the former editorial director of 1105 Media's Education Group and editor-in-chief of THE Journal, STEAM Universe, and Spaces4Learning. A 30-year publishing veteran, Nagel has led or contributed to dozens of technology, art, marketing, media, and business publications.

He can be reached at [email protected]. You can also connect with him on LinkedIn at https://www.linkedin.com/in/davidrnagel/ .


Featured

  • teacher and children working with a LEGO Education Science kit

    LEGO Education Debuts Science Kits for Hands-on Learning

    LEGO Education has announced a new learning solution to engage students in hands-on science learning. Available in three kits by grade band, LEGO Education Science provides 120-plus standards-aligned science lessons, teacher materials, and select LEGO bricks and hardware.

  • computer monitor with glowing digital data and graphs bursting out in an abstract, energetic explosion of lines and elements against a dark background

    New OpenAI Agent Turns ChatGPT into a Research Analyst

    OpenAI has unveiled a new "Deep Research" feature that enhances ChatGPT with the capabilities of a "research analyst" that automates time-consuming research by retrieving, analyzing, and synthesizing online information.

  • silhouetted human figures stand opposite a glowing digital brain, surrounded by abstract circuits and shadowy shapes

    Tech Execs Expect AI Advancements to Increase Security Threats

    Forty-one percent of tech executives in a recent international survey said they believe advancements in AI will significantly increase security threats. NetApp's second annual Data Complexity Report points to 2025 as "AI's make or break year."

  • outline of a modern school building as glowing blue geometric shapes, surrounded by binary code streams, with golden orbs and lines representing funding, set against a dark gray gradient with faint grid patterns

    FCC Cybersecurity Pilot Participants Selected

    The Federal Communications Commission has officially selected the participants for its Schools and Libraries Cybersecurity Pilot, the three-year program exploring the use of Universal Service funds to improve school and library defenses against cyber attacks.