[Libs-Or] Tech-Talk: EXCEL / G-Sheets – COUNTA, COUNTIF, Count What?

Wed Aug 28 10:19:31 PDT 2019

Greetings and welcome to this week’s issue of Tech-Talk!

Having trouble reading this email? You can view the both the tech tip<https://www.tech-talk.com/counta-countif-count-what.html> and the communication tip<https://www.tech-talk.com/make-it-a-habit-to-pass-it-on.html> online instead! When prompted for a username and password, use ORLIBTECH for BOTH.
Do you know these Excel "Counting" formulas?
[http://r20.rs6.net/on.jsp?ca=35b5da7a-c131-4199-b5a1-871ba11898da&a=1100778316270&c=980e0ed0-0713-11e6-a7b7-d4ae528ed502&ch=981cb4d0-0713-11e6-a7b7-d4ae528ed502]

[http://files.constantcontact.com/ee1208b4001/34916cb2-412f-43fa-8294-1f7ecb0e9a3d.png]

[https://files.constantcontact.com/ee1208b4001/4fec0b02-4e14-4142-b18e-a171347a0c3b.png]

Have You Registered Yet?

A 3-Part Webinar Series
for
OREGON Library Staff
(It's free!)

"Become a Tech Hero and a Power Communicator"

Register Here<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk-GYJAh1ksDzsQPTMoR_tKfKpYHW1JYEY1SccGv_wzr6u_L45mgNqD0rX9gvRMkA-C6yerJj6VNJdCxSHufFc6bkAP1GHY5Hy0Q343J6PLD4v_PAdPT7eJS1vqEuhnLX-bYej2FTq9UiIbHVbX6VQuQ=&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>!

 This Week's Topic: EXCEL / Google Sheets
1. VIDEO ... COUNTA, COUNTIF, Count What?
2. ARTICLE ... COUNTA, COUNTIF, Count What?
3. COMMUNICATING ... Passing it on

[Counting formulas]<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk9nfd5K11Z5ZsKRUtiNx8vb5wow4YK6EdLbpGuwWG8PoMINnPHhhFT6ZjbVZxnJD-jU4FFIABQHH_YSGOiFlkFA2WmlYqVTeEbW9C2WdQ1frVQFsDgURUFs_Y890cwTXCI5BPhUIhYor&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>

Photo by Marco Secchi<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk9nfd5K11Z5ZkdQwwoxGXYYI8R-7gnlsrlY5NY57LbMpz5aytxjWfkcxdaoVwmtMhIdhl1EZxylza3YL9Bl9sGGNQaLpe_M_2All6AnFDhf1ZobUqRSufN8-8ld3s0dV2VRZ-GQbWbukFWoSHF3LhR8g9kDNu2qqAo3oRuvAFCl8_JDA_d1wGvWpaVPEqz5DsQiuZNdYitHtdlYXu8l0mQw=&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==> on Unsplash<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk9nfd5K11Z5Z0gRH14oeUkGKxIXz0tcmWgJB3I5deHPbJ_U1T7Z06NwBGCuBNPoviGVm2cybd1RaE3p_xJFNuyoZhOmzisTMlyeU8to3OG908G7h4jxGLWtDcFQzVfiEtCrrqPUyIs4iWpR3_lxhhLBJFbbGdoV2oO5VHaY3QrRtcdj-PBS4WCWTvktYdZJ-Jz6cbd7oJqQpnaMckT9D4YZC2H06m0XE9HvvniJmTpbZ&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>

Brought To You By State Library of Oregon

[State of Oregon]

Visit Tech-Talk.com Database<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk_lagUevvbDhxWhGROChCcqtl7hXAMyC2SBIBTdyytT9_u45YSa5M4VbmM3ojLlz1sG_Sz9eXmDV-i2N6Gvq0I8VligaQqvsdSIWcOgUeFQg&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>

Username: ORLIBTECH
Password: ORLIBTECH

Questions about Tech-Talk?
darci.hanning at state.or.us

EXCEL / Google Sheets – COUNTA, COUNTIF, Count What?
Intermediate

[survey]

I was looking at some survey results that were pulled into an Excel spreadsheet. I needed to calculate the number of people that answered a question, those that left it blank, as well as compute results for some questions that were in text, not a number.

I use formulas all the time to add up columns/rows, compute averages, etc... but these calculations are a little different. I know that Excel is powerful, so it is just a matter of learning the correct ones.

These functions may not be used every day, but they are very handy when you do need them. We'll call them the "Counting" formulas. Let's take a look at the most common four and how you would use them.

Counting Formulas

Excel has a set of formulas that start with "COUNT..." These functions help you to automatically count the number of cells in a range that are blank, have data or contain certain types of data.

·     COUNT: Counts cells that contain numbers (only numbers)
·     COUNTA: Counts cells that contain anything (numbers and text)
·     COUNTBLANK: Count cells that are blank (empty)
·     COUNTIF: Counts cells that meet a specified/single criteria

Now that we understand what they are and do, let's play with these formulas.

Using COUNT

Again, COUNT tells you how many of the cells (that you specify) have numbers in them (COUNTA will tell you how many cells are filled with any type of data).

In this example we want to count the cells that have numbers in them - showing which people answered a question. It doesn't add them. It's looking for the number of responses.

[count]

Since they are numbers, we can use the COUNT formula.

·     First, place your cursor in the cell where you want the results displayed.

·     In the cell (or in the formula bar at the top), type =COUNT(range of cells). In this case based on the range of cells it would be =COUNT(B2:B15).

·     Click Enter.

The results will be displayed. In this example, we have eight people that answered the question.

[results]

COUNTA

Use COUNTA in situations where there is any type of data in the cells -- like text.

Below in the survey results, we want to see how many people answered with each type of response (Bad, Satisfactory, Good, Great).

[Counta]

·     First, to make it easier to view so that all of the answers are together, we sorted by answer type.

·     Next place your cursor in the cell where you want the results displayed. In this example, we want to see the results after each section of answers.

·     In the cell (or in the formula bar at the top), type =COUNTA(range of cells). In this example, for the first set of answers for the response "Bad" -- the range of cells it would be =COUNTA(B2:B3)

·     Hit Enter.

After we completed the formula for the other three survey responses we have a number displayed that represents how many people responded with each answer type.

[counta]

COUNTBLANK

[count]

Using this same question example, now we want to count the blank cells (the number of people that did not answer the question).

·     Place your cursor in the cell where you want the results displayed.

·     In the cell (or in the formula bar at the top), type =COUNTABLANK(range of cells). In this case based on the range of cells it would be =COUNTBLANK(B2:B15)

·     Hit Enter.

The results will be displayed. In this example, we have six people who did NOT answer the question.

[https://files.constantcontact.com/ee1208b4001/7234ed0d-25f1-4563-801d-72ee446c71fb.png]

COUNTIF

The COUNTIF formula helps you to "count IF a cell meets a criteria."

So in our survey, we have a question with four possible answers: Bad, Satisfactory, Good and Great. We want to calculate how many of each answer was reported.

We'll start by adding the formula to the first answer, Bad. Then we can copy the formula and just replace the answer for the other three.

In this formulas we need to not only add the function and range of cells, but the "criteria" as well (Bad, Satisfactory, Good or Great).

[Countif]

·     Place your cursor in the cell where you want the results displayed.

·     In the cell (or in the formula bar at the top), type =COUNTIF(range of cells, "criteria"). In this example, for "Bad" our formula would look like =COUNTIF(B2:B15,"Bad")

·     Hit Enter.

Now we’ll simply copy the formula into the other three cells and replace the criteria with Satisfactory, Good and Great.

Our results show the number of people that answered with each particular answer.

[countif]

Another Way to Add These “Counting” Formulas

If you don't remember the actual formula, but the "name"...

·     Place your cursor in the cell where you want it displayed and in the menu go to the Formulas tab, click More Functions, select Statistical, and then click one of the functions.

[Count]

·     Then in the Function Arguments box, Value1, enter the range of cells. Then click OK.

[https://files.constantcontact.com/ee1208b4001/ff40a761-77f0-4f45-8b33-ef41d0f8f789.png]

Your formula results will be displayed.

[Google Sheets]

Google Sheets

Good News! If you use Google Sheets, these Counting formulas work exactly the same.

Follow the steps above to automatically count the number of cells in a range that are blank, contain data or contain certain types of data.

So, no matter if you are in Excel or Google Sheets, you can use these formulas to count in a number of different ways.

Another Idea

If you do this type of thing often, you may also want to consider making it simpler by creating a Pivot Table.

(See the Tech-Talk Treasure Hunt: Pivot Tables<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlk9nfd5K11Z5ZtY-_ma2BWgsES5Oi3amutJSdbd_MQtJ7azjceAUpNsV733QabMuVz4IyV9BpF_IRj9saITsPtjs-4MZY_BGDscNR_wE2CxuJx54m8rCgZLNZE_i4caS53JUg3HQ6a8ZOfJSoztVJAMs=&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>.)

[http://files.constantcontact.com/ee1208b4001/f8f1ecc8-4884-4c21-8ed0-cf647e6db944.png]

Communication: Principles
Make it a habit to "pass it on"

Effective communication means passing the information on to others. Don't assume they already know. When we pass information on, we empower each other.

For example, you receive Tech-Talk. What do you do with it?

Every library license allows, and encourages, you to:

·     SHARE with every librarian, library staff, library volunteer.
·     USE THE CONTENT in developing classes for patrons and staff.
·     HELP PATRONS to solve problems, answer questions with the information you've learned and can find for them in the Tech-Talk Database.

So if others in your library are not getting Tech-Talk, pass it on!

TIP: If you want to give Tech-Talk to patrons, students and faculty, libraries can get an unlimited license to do that by contacting us<mailto:support at tech-talk.com>.

What about other important emails and documents that you get at work? When you look at the distribution, are there others who would benefit if you passed it on?

Think inclusive! Be a communication facilitator. Think, "passing it on" as a matter of routine.

[http://files.constantcontact.com/ee1208b4001/b13a7a6a-7445-4d8e-b741-4b475385ef5b.png]<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlkwv-m5ELlZmXlgXE_Xq2WbJ_fiWkj_RvEkyfCSVfNMTaWYRIKJPlT2pf8NKgajw1MTtObIUQBHrTy2X2uLwLgSL9rS9rSPqg0V2blJ9pS_fI&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>

[Ask a question]<http://r20.rs6.net/tn.jsp?f=001Xll4kKeM-_kWTdSTIJMxDQTEB7_yE52nX6gtFjZbmri7TLxSiCJlkwGQKcgXPyVcqiRUEP89U84lfyr_jbOaU_mSfbyWzxlfFn8MxxDYdJyFp61_C6_6WKQZWHLGcRHocm_3uP-SufBGK8aItj_fQqDrg7QjSh8HLWyzomdEuWU=&c=1Hvl0kulQha5EmOpSTAxNlXBUdgVByUWHYfWq6TpnyeouhsV39xVTA==&ch=eFFXaLtIgrfHtbepbxKoVkx1nqF6Jc1z958tgrwfFcGEjy7QZkc7sw==>

Copyright 1996-2019 Shared Results International. Published weekly. Do not forward this issue of Tech-Talk.com without prior permission from Shared Results International. Distribution is limited by contract. Forwarding it to unauthorized recipients constitutes copyright infringement.
For information on site licenses or how to include additional recipients, email darci.hanning at state.or.us<mailto:darci.hanning at state.or.us>.

The Tech-Talk e-newsletter for Oregon library staff is distributed weekly via the Libs-OR and TechTalk mailing lists. Please do not forward. Know someone who might be interested in receiving a copy but they’re not on Libs-OR? Have them contact darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> or visit http://listsmart.osl.state.or.us/mailman/listinfo/techtalk to subscribe to just the Tech-Talk newsletter!

Tech-Talk is a paid subscription service for staff of Oregon libraries and is supported in whole by the Institute of Museum and Library Services (IMLS) through the Library Services and Technology Act (LSTA), administered by the State Library of Oregon.

Cheers,
Darci Hanning, MLIS
Public Library Consultant / CE Coordinator
darci.hanning at state.or.us<mailto:darci.hanning at state.or.us> | 503-378-2527| www.oregon.gov/library<https://www.oregon.gov/library>
Follow us: Facebook<http://fb.me/StateLibraryOR> | Twitter<https://twitter.com/StateLibraryOR> | Tumblr<http://www.statelibraryor.tumblr.com/> | Pinterest<https://pinterest.com/statelibraryor/>

[cid:image005.png at 01D31C27.4B9FEA30]

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20190828/fe70c7e1/attachment.html>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: image001.png
Type: image/png
Size: 15548 bytes
Desc: image001.png
URL: <https://omls.oregon.gov/pipermail/libs-or/attachments/20190828/fe70c7e1/attachment.png>