Formulas that refer to other colums for arguments?

[audreyglennette]

How can I write a formula that will count all null cells within a column, but
only if another column is also null for that row?

Situation: I am using Excel to track training of personnel for the
company.(I know, it would probably be better done in Access. Still teaching
myself Access though. So for the time being, I am keeping it in Excel, and
will convert to Access at a later date.) I need to see who still needs to be
trained in a certain course. Problem is that some students no longer work for
the company. I want to eliminate these people from the "needs training" tally
without removing them from the entire worksheet. I have it set up so that the
courses is are in columns and students in rows. Also in columns I have placed
personnel information, such as SS#, check in date, nicknames, etc. One
column is labeled for "checked out"; as in, they don't work here anymore.
This is the column I need to refer to for arguments. I want write a formula
that will count all blank cells for the specific course column, so long as
the "Checked Out" cell for that person is null (empty) too. Is this possible?

Let me know if I need to better explain. Thanks!

 

[Duke Carey]

try

=sumproduct(isblank(column for needs testing)*isblank(column for gone))

Note - this works only for cells that are truly empty - no spaces, no
formulas that return ""

 

[Monkito]

Try this

=countblank(RANGE)-counta(RANGE)

were countblank refers to course and counta refers to checked out

 

[audreyglennette]

This returns a total of 0. SUMPRODUCT? I thought that a SUMPRODUCT would add
all the cells that meet the argument's criteria together. If I am correct,
that would mean that the return would always be 0, as all the arguments refer
to empty cells!? Am I right here? I think I need a COUNTBLANK formula??? What
do you think?

 

[Duke Carey]

Let's say your data is in columns C & D. If a cell in D is blank it means
testing is needed. If C is blank it means the person is still an employee.
This formula does NOT return a zero UNLESS the cells that appear blank have
spaces or some entry that only LOOKS blank

=SUMPRODUCT(ISBLANK(D150)*ISBLANK(C1:C50))

make sure you use the * sign between the 2 isblanks, not a comma. Using a
comma WILL RETURN 0

 

[audreyglennette]

YES!Thank you, thnak you! That works! I'm not sure what I had typed wrong the
first time I tried it. THANKS...here's what I had come up with; it seemed to
work too. It returns the same answer; and then I tred it with a much smaller
set of data (so I could check it by hand) and it seemed to work. What do you
think? Does mine have flaws?

=SUM(IF(ISBLANK(W2:W1199),1,0)*IF(ISBLANK(M2:M1199),1,0))

 

[audreyglennette]

Problem here is that I only want to count the blank ones in teh course column
that are also blank in the checked out column. I think your formula will
count all the blanks in the course and then subtract the total number of
blank ones in the checked out column. You see what I mean? I only want a
total of students who are blank in both columns.