problem with COUNTIF statements

  • Thread starter Thread starter JMAKOUTZ
  • Start date Start date
J

JMAKOUTZ

Greetings.

I just want to thank everyone in advance for helping. I figured i
would be no problem to do what I wanted but after 3 days of beating m
head against the wall I figured I would bring in the professiona
help.

Here is the problem:
I am working on a staffing spreadsheet (Excel 2000) that has th
following stats
column A B q r
row 1 ft | Joe | total FT | total FT avail
2 pt | Jane | total PT | total PT avail
3 pt |
ect..ect..ect

column A lists employee status: FT for Full Time or PT for PT
column B lists employee name or nothing for an available spot

so here is what i want to do:
count if there is a empty spot AND subract from the total headcount i
its part time or Full Time.
when I punch up the countif statement I get a error stating that I hav
to many variables
here is the statement I wrote to count the totals:
=COUNTIF(A1:O42,"PT") + COUNTIF(A1:O42,"PT-ADMIN")

=COUNTIF(A1:O42,"FT") + COUNTIF(A1:O42, "SENIOR") + COUNTIF(A1:O42
"SUPERVISOR") + COUNTIF(A1:O42,"FT-ADMIN")

These work fine to get me total FT and PT employees but how can I ge
it to subtract the "" entries from the right column?
I am attaching the file for your perusial

Attachment filename: 014 staffing.xls
Download attachment: http://www.excelforum.com/attachment.php?postid=54026
 
You could create a non-contiguous range, named "NameCells" of all of the
cells that are supposed to have a Name in them, and instead of a blank in
the ones without names, you could put a 1, even if it was the same color as
the background to hide it, then just do =COUNT(NameCells) to get a count of
all the cells in the range without a name in them..........then, do the
additional math to come up with whatever results you're looking
for...........

hth
Vaya con Dios,
Chuck, CABGx3
 
JMAKOUTZ said:
bump. can anyone help or at least give a few suggestions?
Click to expand...

Hi,

I'm not sure that this is what you want, but try:

=SUM(IF(A1:A6="FT",1,0)) Total FT
=SUM(IF((A1:A6="FT")*(B1:B6<>""),1,0)) Total FT less empty spots
=SUM(IF(A1:A6="PT",1,0)) Total PT
=SUM(IF((A1:A6="PT")*(B1:B6<>""),1,0)) Total PT less empty spots

Adjust the range accordingly and enter these formulas using
CTRL+SHIFT+ENTER.

Is this what you're looking for?

Hope this helps!
 
Hi
not really sure what you mean with empty spot. what is this right
column?.
You may post soime more rows of data and explain your expected result
based on this example.
I would assume that SUMPRODUCT is what you're looking for
 
Back
Top