How do I do a count only if there is data

Posted on 2013-08-28
Medium Priority
Last Modified: 2013-08-29
I have an excel spreadsheet with the following columns:

PROGRAMMERS                                       2.00
                                     Billy                    3.50
                                     Bobby                 4.00
INSTALLERS                                              6.00

I want to count how many technicians will be working, but only if they have hours in the second column.  So I would have a cell that would see that Billy and Bobby have hours applied, but Jimmy does not so my cell would say there are 2 technicians.  If I added some hours to Jimmy, then the cell would count 3 technicians.

How do I do this?  Multiple countif criteria?  Sumproduct?

Question by:Kevin Smith
  • 3
  • 2
LVL 23

Assisted Solution

NBVC earned 800 total points
ID: 39445879
A little confused about your setup.

Are the names in the same column as Programmers, Technicians, Installers?

Or in separate columns... if so, then maybe

e.g. =COUNTIFS(B1:B10,"<>",C1:C10,"<>")

counts how many non blanks in B1:B10 match non blanks in C1:C10
LVL 12

Accepted Solution

Harry Lee earned 1200 total points
ID: 39446011
For your exact example, Nothing is going to work at all. Your column A is not filled. Next to the 3 names Billy Jimmy Bob, there is no data to show that they are Technicians. So, fill the Column A according to their Employee Class. Then, use the following formula.


Same thing to the Column B. Only the Technicians section have names. You should fill it so there is no blanks.

So for each line that has hours, there should always be Employee Class, and Employee Name.

NB_VC's formula works only because there are 3 names in Column B. If all sections of employee classes are filled, there would be problem. It will not be only counting Technicians but will count every line that has hours and have a name next to it.
LVL 23

Expert Comment

ID: 39446240
This is why i mentioned off the bat that I was confused about the setup and asking questions...
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.


Author Comment

by:Kevin Smith
ID: 39446679
I see what you mean.  I put "tech source" as a constant in the first column and the formula worked fine...I know NBVC began the answer on the right path and I wasn't as quick to respond as I should've been...what's the best way to divvy up points?
LVL 23

Expert Comment

ID: 39446755
Divvy them as you see fit based on the which answer got you closest to the solution and which assisted further.

Author Closing Comment

by:Kevin Smith
ID: 39448565
Thanks for the help!

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this article, we will see two different methods to recover deleted data. The first option will be using the transaction log to identify the operation and restore it in a specified section of the transaction log. The second option is simpler and c…
Read this tutorial to learn how to fix repeating password error prompts when setting up Gmail IMAP with Microsoft Outlook. The entire process is described with step by step, illustrated instructions. Enjoy...
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question