Solved

# How do I do a count only if there is data

Posted on 2013-08-28
Medium Priority
193 Views
I have an excel spreadsheet with the following columns:

PROGRAMMERS                                       2.00
TECHNICIANS
Billy                    3.50
Jimmy
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?

Thanks!
0
Question by:Kevin Smith
• 3
• 2

LVL 23

Assisted Solution

NBVC earned 800 total points
ID: 39445879

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
0

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.

=COUNTIFS(A1:A6,"=Technicians",C1:C6,"<>")

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.
0

LVL 23

Expert Comment

ID: 39446240
This is why i mentioned off the bat that I was confused about the setup and asking questions...
0

Author Comment

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?
0

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.
0

Author Closing Comment

ID: 39448565
Thanks for the help!
0

## Featured Post

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.