software

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 quot;needs trainingquot; 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 quot;checked outquot;; 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 quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?

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

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 quot;quot;

quot;audreyglennettequot; wrote:

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

Try this

=countblank(RANGE)-counta(RANGE)

were countblank refers to course and counta refers to checked out

audreyglennette wrote:
gt; How can I write a formula that will count all null cells within a column, but
gt; only if another column is also null for that row?
gt;
gt; Situation: I am using Excel to track training of personnel for the
gt; company.(I know, it would probably be better done in Access. Still teaching
gt; myself Access though. So for the time being, I am keeping it in Excel, and
gt; will convert to Access at a later date.) I need to see who still needs to be
gt; trained in a certain course. Problem is that some students no longer work for
gt; the company. I want to eliminate these people from the quot;needs trainingquot; tally
gt; without removing them from the entire worksheet. I have it set up so that the
gt; courses is are in columns and students in rows. Also in columns I have placed
gt; personnel information, such as SS#, check in date, nicknames, etc. One
gt; column is labeled for quot;checked outquot;; as in, they don't work here anymore.
gt; This is the column I need to refer to for arguments. I want write a formula
gt; that will count all blank cells for the specific course column, so long as
gt; the quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?
gt;
gt; Let me know if I need to better explain. Thanks!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?

quot;Duke Careyquot; wrote:

gt; try
gt;
gt; =sumproduct(isblank(column for needs testing)*isblank(column for gone))
gt;
gt; Note - this works only for cells that are truly empty - no spaces, no
gt; formulas that return quot;quot;
gt;
gt; quot;audreyglennettequot; wrote:
gt;
gt; gt; How can I write a formula that will count all null cells within a column, but
gt; gt; only if another column is also null for that row?
gt; gt;
gt; gt; Situation: I am using Excel to track training of personnel for the
gt; gt; company.(I know, it would probably be better done in Access. Still teaching
gt; gt; myself Access though. So for the time being, I am keeping it in Excel, and
gt; gt; will convert to Access at a later date.) I need to see who still needs to be
gt; gt; trained in a certain course. Problem is that some students no longer work for
gt; gt; the company. I want to eliminate these people from the quot;needs trainingquot; tally
gt; gt; without removing them from the entire worksheet. I have it set up so that the
gt; gt; courses is are in columns and students in rows. Also in columns I have placed
gt; gt; personnel information, such as SS#, check in date, nicknames, etc. One
gt; gt; column is labeled for quot;checked outquot;; as in, they don't work here anymore.
gt; gt; This is the column I need to refer to for arguments. I want write a formula
gt; gt; that will count all blank cells for the specific course column, so long as
gt; gt; the quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?
gt; gt;
gt; gt; Let me know if I need to better explain. Thanks!

Let's say your data is in columns C amp; 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 0quot;audreyglennettequot; wrote:

gt; This returns a total of 0. SUMPRODUCT? I thought that a SUMPRODUCT would add
gt; all the cells that meet the argument's criteria together. If I am correct,
gt; that would mean that the return would always be 0, as all the arguments refer
gt; to empty cells!? Am I right here? I think I need a COUNTBLANK formula??? What
gt; do you think?
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; try
gt; gt;
gt; gt; =sumproduct(isblank(column for needs testing)*isblank(column for gone))
gt; gt;
gt; gt; Note - this works only for cells that are truly empty - no spaces, no
gt; gt; formulas that return quot;quot;
gt; gt;
gt; gt; quot;audreyglennettequot; wrote:
gt; gt;
gt; gt; gt; How can I write a formula that will count all null cells within a column, but
gt; gt; gt; only if another column is also null for that row?
gt; gt; gt;
gt; gt; gt; Situation: I am using Excel to track training of personnel for the
gt; gt; gt; company.(I know, it would probably be better done in Access. Still teaching
gt; gt; gt; myself Access though. So for the time being, I am keeping it in Excel, and
gt; gt; gt; will convert to Access at a later date.) I need to see who still needs to be
gt; gt; gt; trained in a certain course. Problem is that some students no longer work for
gt; gt; gt; the company. I want to eliminate these people from the quot;needs trainingquot; tally
gt; gt; gt; without removing them from the entire worksheet. I have it set up so that the
gt; gt; gt; courses is are in columns and students in rows. Also in columns I have placed
gt; gt; gt; personnel information, such as SS#, check in date, nicknames, etc. One
gt; gt; gt; column is labeled for quot;checked outquot;; as in, they don't work here anymore.
gt; gt; gt; This is the column I need to refer to for arguments. I want write a formula
gt; gt; gt; that will count all blank cells for the specific course column, so long as
gt; gt; gt; the quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?
gt; gt; gt;
gt; gt; gt; Let me know if I need to better explain. Thanks!

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))

quot;Duke Careyquot; wrote:

gt; Let's say your data is in columns C amp; D. If a cell in D is blank it means
gt; testing is needed. If C is blank it means the person is still an employee.
gt; This formula does NOT return a zero UNLESS the cells that appear blank have
gt; spaces or some entry that only LOOKS blank
gt;
gt; =SUMPRODUCT(ISBLANK(D150)*ISBLANK(C1:C50))
gt;
gt; make sure you use the * sign between the 2 isblanks, not a comma. Using a
gt; comma WILL RETURN 0
gt;
gt;
gt; quot;audreyglennettequot; wrote:
gt;
gt; gt; This returns a total of 0. SUMPRODUCT? I thought that a SUMPRODUCT would add
gt; gt; all the cells that meet the argument's criteria together. If I am correct,
gt; gt; that would mean that the return would always be 0, as all the arguments refer
gt; gt; to empty cells!? Am I right here? I think I need a COUNTBLANK formula??? What
gt; gt; do you think?
gt; gt;
gt; gt; quot;Duke Careyquot; wrote:
gt; gt;
gt; gt; gt; try
gt; gt; gt;
gt; gt; gt; =sumproduct(isblank(column for needs testing)*isblank(column for gone))
gt; gt; gt;
gt; gt; gt; Note - this works only for cells that are truly empty - no spaces, no
gt; gt; gt; formulas that return quot;quot;
gt; gt; gt;
gt; gt; gt; quot;audreyglennettequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; How can I write a formula that will count all null cells within a column, but
gt; gt; gt; gt; only if another column is also null for that row?
gt; gt; gt; gt;
gt; gt; gt; gt; Situation: I am using Excel to track training of personnel for the
gt; gt; gt; gt; company.(I know, it would probably be better done in Access. Still teaching
gt; gt; gt; gt; myself Access though. So for the time being, I am keeping it in Excel, and
gt; gt; gt; gt; will convert to Access at a later date.) I need to see who still needs to be
gt; gt; gt; gt; trained in a certain course. Problem is that some students no longer work for
gt; gt; gt; gt; the company. I want to eliminate these people from the quot;needs trainingquot; tally
gt; gt; gt; gt; without removing them from the entire worksheet. I have it set up so that the
gt; gt; gt; gt; courses is are in columns and students in rows. Also in columns I have placed
gt; gt; gt; gt; personnel information, such as SS#, check in date, nicknames, etc. One
gt; gt; gt; gt; column is labeled for quot;checked outquot;; as in, they don't work here anymore.
gt; gt; gt; gt; This is the column I need to refer to for arguments. I want write a formula
gt; gt; gt; gt; that will count all blank cells for the specific course column, so long as
gt; gt; gt; gt; the quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?
gt; gt; gt; gt;
gt; gt; gt; gt; Let me know if I need to better explain. Thanks!

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.

quot;Monkitoquot; wrote:

gt; Try this
gt;
gt; =countblank(RANGE)-counta(RANGE)
gt;
gt; were countblank refers to course and counta refers to checked out
gt;
gt; audreyglennette wrote:
gt; gt; How can I write a formula that will count all null cells within a column, but
gt; gt; only if another column is also null for that row?
gt; gt;
gt; gt; Situation: I am using Excel to track training of personnel for the
gt; gt; company.(I know, it would probably be better done in Access. Still teaching
gt; gt; myself Access though. So for the time being, I am keeping it in Excel, and
gt; gt; will convert to Access at a later date.) I need to see who still needs to be
gt; gt; trained in a certain course. Problem is that some students no longer work for
gt; gt; the company. I want to eliminate these people from the quot;needs trainingquot; tally
gt; gt; without removing them from the entire worksheet. I have it set up so that the
gt; gt; courses is are in columns and students in rows. Also in columns I have placed
gt; gt; personnel information, such as SS#, check in date, nicknames, etc. One
gt; gt; column is labeled for quot;checked outquot;; as in, they don't work here anymore.
gt; gt; This is the column I need to refer to for arguments. I want write a formula
gt; gt; that will count all blank cells for the specific course column, so long as
gt; gt; the quot;Checked Outquot; cell for that person is null (empty) too. Is this possible?
gt; gt;
gt; gt; Let me know if I need to better explain. Thanks!
gt;
gt;