Formulas that refer to (several) other columns for arguments...

[audreyglennette]

Here was my original question and situation:

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

We came up with these formulas to count the blanks(both seem to work):
=SUMPRODUCT(ISBLANK(P21199)*ISBLANK($A$2:$A$1199))
or
=SUM(IF(ISBLANK(P21199),1,0)*IF(ISBLANK($A$2:$A$1199),1,0))

NEW QUESTION:
I need to add a third column as a arguments, only this time I don't need it
to check for if it is blank, but for a specififc value ("Y"). Here is what I
had tried, but Neither worked
=SUM(IF(ISBLANK(P21199),1,0)*IF(ISBLANK($A$2:$A$1199),1,0)*IF($N$2:$N$1199,"Y"))
and
=SUMPRODUCT(ISBLANK(P21199)*ISBLANK($A$2:$A$1199)*IF($N$2:$N$1199,"Y"))

Any help would be greatly appreciated!

 

[Duke Carey]

You almost had it. Try this instead

=SUMPRODUCT(ISBLANK(P21199)*ISBLANK($A$2:$A$1199)*($N$2:$N$1199="Y"))

 

[audreyglennette]

It returns a 0. I copied and pasted it just as it was...I know there are
several that I can see on the present screen that should fit the criteria, so
0 can't be right. Any other ideas? I'm stumped...I've been working at it for
what seems like hours now.

 

[Duke Carey]

Audrey -

If I paste that EXACT formula into a blank spreadsheet then enter Y into N2
the formula returns a 1. For each Y I add in column N the formula increments
by 1. Therefore I cannot fathom why it doesn't work for you.

Having said that, I notice that the first address range in the formula uses
relative references while the other 2 are absolute references. Is there a
chance that column P isn't the correct column for the first part?

 

[audreyglennette]

You're right! I'm so sorry! That last range should be O not N... there is not
a single "Y" in the N column.

The Absolute references: The first column refers to the course and I want to
auto complete/copy the formula across to all the other courses (Q,R,S,T,
etc.), but the other two criteria(Checked out? and Contracted Employee?) need
be absolute to those specific columns.

Thank you so much. You have been a great help with this project!

 

[Brian]

While you all are on the topic...I am having lots of trouble regarding
formulas between worksheets and multiple columns. Let me try and explain...

On one worksheet, I would like to have a formula that counts cells of
another worksheet that have data in them, but only if a few criteria are
satisfied in each row of the 2nd worksheet. For example, I want to generate a
number of entries in column C, only if column B and/or A meet certain
criteria for each specific row in that same worksheet. So, in cell C1, I
would like to know if there is some data present, but only if cell B1 meets
certain criteria and A1 meets certain criteria (say the date in A1 must be
todays date, otherwise I don't care...or the name in B1 must be a certain
name). And, suppose we are talking about the first 5 rows...so I am
ultimately looking for a SUM. How would I show that SUM in a specific cell on
another worksheet?

I hope I explained that well enough. Thanks in advance for any help!