Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

try:

=IF(ISERROR(SMALL(IF(('New Rule Clients'!$Y$4:$Y$3500="Yes")*('New Rule
Clients'!$Z$4:$Z$3500=""),ROW('New Rule
Clients'!$Y$4:$Y$3500),""),ROW($A1))-ROW($A$4)+1),"",INDEX('New Rule
Clients'!A$4:A$3500,N(SMALL(IF(('New Rule Clients'!$Y$4:$Y$3500="Yes")*('New
Rule Clients'!$Z$4:$Z$3500=""),ROW('New Rule
Clients'!$Y$4:$Y$3500),""),ROW($A1))-ROW($A$4)+1)))
Entered with Ctrl+Shift+Enter

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

Thanks for the suggestion Toppers. Unfortunately the formula didn't take :(

Dan

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

On limited testing I did, it worked.

What/why didn't it take?

Did you enter with Ctrl+Shift +Enter?

Can you send sample data as it's difficult to test in isolation?

toppers <at nospamjohntopley.fsnet.co.uk

remove nospam

"Dan the Man" wrote:

Thanks for the suggestion Toppers. Unfortunately the formula didn't take :(

Dan

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

I spoke to soon Toppers. I did get it to work. The sole problem was that only
the first of the 11 names (the one identified in cell A4) which meet the
criteira of the formula (YES in Row Y, and NO DATE in Row Z) appear. Because
I know that there are 11 folk who meet the criteria, I was hoping the formula
outcome would generate all of their names? I didn't know if this is too
complicated a task for Excel to handle.

Dan

"Toppers" wrote:

On limited testing I did, it worked.

What/why didn't it take?

Did you enter with Ctrl+Shift +Enter?

Can you send sample data as it's difficult to test in isolation?

toppers <at nospamjohntopley.fsnet.co.uk

remove nospam

"Dan the Man" wrote:

Thanks for the suggestion Toppers. Unfortunately the formula didn't take :(

Dan

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

Copy the formula down until you get a blank: I didn't make this clear. It
should display all names in concurrent rows.

"Dan the Man" wrote:

I spoke to soon Toppers. I did get it to work. The sole problem was that only
the first of the 11 names (the one identified in cell A4) which meet the
criteira of the formula (YES in Row Y, and NO DATE in Row Z) appear. Because
I know that there are 11 folk who meet the criteria, I was hoping the formula
outcome would generate all of their names? I didn't know if this is too
complicated a task for Excel to handle.

Dan

"Toppers" wrote:

On limited testing I did, it worked.

What/why didn't it take?

Did you enter with Ctrl+Shift +Enter?

Can you send sample data as it's difficult to test in isolation?

toppers <at nospamjohntopley.fsnet.co.uk

remove nospam

"Dan the Man" wrote:

Thanks for the suggestion Toppers. Unfortunately the formula didn't take :(

Dan

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan

Excellent Toppers! That did it. Thanks for the help!


"Toppers" wrote:

Copy the formula down until you get a blank: I didn't make this clear. It
should display all names in concurrent rows.

"Dan the Man" wrote:

I spoke to soon Toppers. I did get it to work. The sole problem was that only
the first of the 11 names (the one identified in cell A4) which meet the
criteira of the formula (YES in Row Y, and NO DATE in Row Z) appear. Because
I know that there are 11 folk who meet the criteria, I was hoping the formula
outcome would generate all of their names? I didn't know if this is too
complicated a task for Excel to handle.

Dan

"Toppers" wrote:

On limited testing I did, it worked.

What/why didn't it take?

Did you enter with Ctrl+Shift +Enter?

Can you send sample data as it's difficult to test in isolation?

toppers <at nospamjohntopley.fsnet.co.uk

remove nospam

"Dan the Man" wrote:

Thanks for the suggestion Toppers. Unfortunately the formula didn't take :(

Dan

"Dan the Man" wrote:

Yesterday I got some great help clarifying my spreadsheet need, and the
formula below was generated (which works, and does exactly what it needs to
do):

=SUMPRODUCT(('New Rule Clients'!Y4:Y3500="Yes")*('New Rule
Clients'!Z4:Z3500=""))

I'm wondering however, if it is possible to add a third level of formula and
or logic to the above, based upon the following requirement:

In Column A I list clients by "Last Name". The above formula offers me a
nice "total numerical count" of those individuals who have not had their
final interviews with our program. If I could also get the actual names which
associate with those "numeric values" I would have even more precision. Is
there a way to "link" logic with Column A (where the actual names are
listed), to generate a formula outcome which would provide me the actual
names of clients who still need their final interviews (above and beyond just
identifying that a specific number haven't yet been interviewed?).

Thank You in advance for you Input and Help!

Dan