Write a formula using two arrays, across two worksheets
 

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan

Write a formula using two arrays, across two worksheets
 

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Åke

Write a formula using two arrays, across two worksheets
 

Lars,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those employees
under 54 or equal and those employees with yrs of svc under 9yrs or less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Ã…ke

Write a formula using two arrays, across two worksheets
 

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those employees
under 54 or equal and those employees with yrs of svc under 9yrs or less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Åke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Åke

Write a formula using two arrays, across two worksheets
 

Thank you. This worked on the first cell. But when I copied to another
cell, and changed the E column requirements (for example, changed EU3 to 771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those employees
under 54 or equal and those employees with yrs of svc under 9yrs or less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Ã…ke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Ã…ke

Write a formula using two arrays, across two worksheets
 

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to another
cell, and changed the E column requirements (for example, changed EU3 to 771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those employees
under 54 or equal and those employees with yrs of svc under 9yrs or less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Åke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Åke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Åke

Write a formula using two arrays, across two worksheets
 

Hi, that worked great. Now, one more question-can I add another arguement on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to another
cell, and changed the E column requirements (for example, changed EU3 to 771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for me.
I have two conditions also. I want my formula to compare range f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9); however,
this produces the wrong anwer as well.
I also tried =sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those employees
under 54 or equal and those employees with yrs of svc under 9yrs or less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information from one
column in a different worksheet (same workbook) based on a condition, then
using the results to further look another column and get results based on
another condition.
For example, I want to know how many times EU3 appears in column E, and then
of those appearances then find out how many of those are <4 in column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can you
expect the result that three of them has a value less than 4 in column
N? Have I missed anything?

Hope this helps / Lars-Ã…ke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Ã…ke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Ã…ke

Write a formula using two arrays, across two worksheets
 

Yes, you can.
--
David Biddulph

"Susan" wrote in message
...
Hi, that worked great. Now, one more question-can I add another arguement
on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to another
cell, and changed the E column requirements (for example, changed EU3 to
771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for
me.
I have two conditions also. I want my formula to compare range
f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service
and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9);
however,
this produces the wrong anwer as well.
I also tried
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those
employees
under 54 or equal and those employees with yrs of svc under 9yrs or
less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information
from one
column in a different worksheet (same workbook) based on a
condition, then
using the results to further look another column and get results
based on
another condition.
For example, I want to know how many times EU3 appears in column
E, and then
of those appearances then find out how many of those are <4 in
column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can
you
expect the result that three of them has a value less than 4 in
column
N? Have I missed anything?

Hope this helps / Lars-Åke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Åke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Åke

Write a formula using two arrays, across two worksheets
 

What arguement would I use after <4? An and command?

"David Biddulph" wrote:

Yes, you can.
--
David Biddulph

"Susan" wrote in message
...
Hi, that worked great. Now, one more question-can I add another arguement
on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to another
cell, and changed the E column requirements (for example, changed EU3 to
771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer for
me.
I have two conditions also. I want my formula to compare range
f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of service
and
place my answer which should be 20 on sheet2 b3.
I have tried =Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9);
however,
this produces the wrong anwer as well.
I also tried
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those
employees
under 54 or equal and those employees with yrs of svc under 9yrs or
less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather information
from one
column in a different worksheet (same workbook) based on a
condition, then
using the results to further look another column and get results
based on
another condition.
For example, I want to know how many times EU3 appears in column
E, and then
of those appearances then find out how many of those are <4 in
column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column N

As there are only two EU3 in column E in your sample data, how can
you
expect the result that three of them has a value less than 4 in
column
N? Have I missed anything?

Hope this helps / Lars-Ã…ke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Ã…ke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Ã…ke

Write a formula using two arrays, across two worksheets
 

Well, seeing that you are starting with a SUMPRODUCT, why not just include
another term in that function?
--
David Biddulph

"Susan" wrote in message
...
What arguement would I use after <4? An and command?

"David Biddulph" wrote:

Yes, you can.
--
David Biddulph

"Susan" wrote in message
...
Hi, that worked great. Now, one more question-can I add another
arguement
on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to
another
cell, and changed the E column requirements (for example, changed EU3
to
771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer
for
me.
I have two conditions also. I want my formula to compare range
f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of
service
and
place my answer which should be 20 on sheet2 b3.
I have tried
=Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9);
however,
this produces the wrong anwer as well.
I also tried
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those
employees
under 54 or equal and those employees with yrs of svc under 9yrs
or
less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather
information
from one
column in a different worksheet (same workbook) based on a
condition, then
using the results to further look another column and get
results
based on
another condition.
For example, I want to know how many times EU3 appears in
column
E, and then
of those appearances then find out how many of those are <4 in
column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column
N

As there are only two EU3 in column E in your sample data, how
can
you
expect the result that three of them has a value less than 4 in
column
N? Have I missed anything?

Hope this helps / Lars-Åke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Åke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Åke

Write a formula using two arrays, across two worksheets
 

Ok, I'm not very versed on that, can you tell what term to use?

"David Biddulph" wrote:

Well, seeing that you are starting with a SUMPRODUCT, why not just include
another term in that function?
--
David Biddulph

"Susan" wrote in message
...
What arguement would I use after <4? An and command?

"David Biddulph" wrote:

Yes, you can.
--
David Biddulph

"Susan" wrote in message
...
Hi, that worked great. Now, one more question-can I add another
arguement
on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to
another
cell, and changed the E column requirements (for example, changed EU3
to
771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer
for
me.
I have two conditions also. I want my formula to compare range
f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of
service
and
place my answer which should be 20 on sheet2 b3.
I have tried
=Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9);
however,
this produces the wrong anwer as well.
I also tried
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is those
employees
under 54 or equal and those employees with yrs of svc under 9yrs
or
less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Ã…ke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather
information
from one
column in a different worksheet (same workbook) based on a
condition, then
using the results to further look another column and get
results
based on
another condition.
For example, I want to know how many times EU3 appears in
column
E, and then
of those appearances then find out how many of those are <4 in
column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in column
N

As there are only two EU3 in column E in your sample data, how
can
you
expect the result that three of them has a value less than 4 in
column
N? Have I missed anything?

Hope this helps / Lars-Ã…ke



In the COUNTIF formula you should have " " around the condition.
In the SUMPRODUCT formula you should not have any " " around the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Ã…ke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Ã…ke

Write a formula using two arrays, across two worksheets
 

Well, you've got ...,--(N1:N100<4)... for your test for column N being less
than 4.

For N being not less than 0 you could use
...,--(NOT(N1:N100<0))...
but you'd be better with
....,--(N1:N100=0)...
--
David Biddulph

"Susan" wrote in message
...
Ok, I'm not very versed on that, can you tell what term to use?

"David Biddulph" wrote:

Well, seeing that you are starting with a SUMPRODUCT, why not just
include
another term in that function?
--
David Biddulph

"Susan" wrote in message
...
What arguement would I use after <4? An and command?

"David Biddulph" wrote:

Yes, you can.
--
David Biddulph

"Susan" wrote in message
...
Hi, that worked great. Now, one more question-can I add another
arguement
on
the end, where it asks for <4, to ask for <4 but not less than 0?

Thanks,
Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 14:21:27 -0700, Susan
wrote:

Thank you. This worked on the first cell. But when I copied to
another
cell, and changed the E column requirements (for example, changed
EU3
to
771)
the formula returned a 0 result. Any ideas?

Susan

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 11:21:18 -0700, Vicki
wrote:

Lars,
I tried this formula and it does not produce the correct answer
for
me.
I have two conditions also. I want my formula to compare range
f6:f52 and
g6:g52 on sheet1 for employees who are <=54yo with <=9 yrs of
service
and
place my answer which should be 20 on sheet2 b3.
I have tried
=Countif(sheet1!f6:f52<=54)-Countif(sheet1!g6:g52<=9);
however,
this produces the wrong anwer as well.
I also tried
=sumproduct(--(sheet1!f6:f52<="54"),--(sheet1!g6:g52<="9")).
This too did not work. This formula produces 47, which is
those
employees
under 54 or equal and those employees with yrs of svc under
9yrs
or
less
(regardless of age).

Is countif the right formula?
Please help.

"Lars-Åke Aspelin" wrote:

On Mon, 28 Jul 2008 09:49:14 -0700, Susan
wrote:

What I want to do is write a formula that will gather
information
from one
column in a different worksheet (same workbook) based on a
condition, then
using the results to further look another column and get
results
based on
another condition.
For example, I want to know how many times EU3 appears in
column
E, and then
of those appearances then find out how many of those are <4
in
column N.
(How many times does = less than 4? answer is 3

Column:
E F N O P
EU3 0 0 F Y
EE3 15 14 H Y
579 6 4 H Y
EU3 4 3 K Y
EE3 4 3 H Y

Thanks for any help!
Susan




Try these formulas:

=COUNTIF(E1:E100,"=EU3")
for the number of EU3's in (first 100 rows of) column E


=SUMPRODUCT(- -(E1:E100="EU3"),- -(N1:N100<4))
for the number of EU3's that have a value less than 4 in
column
N

As there are only two EU3 in column E in your sample data,
how
can
you
expect the result that three of them has a value less than 4
in
column
N? Have I missed anything?

Hope this helps / Lars-Åke



In the COUNTIF formula you should have " " around the
condition.
In the SUMPRODUCT formula you should not have any " " around
the
value you compare with.

In the example you wrote you had the other way around.

Hope this helps / Lars-Åke



=SUMPRODUCT(- -(""&E1:E100="xxx"),- -(N1:N100<4))

will work also if xxx happens to be a number like 771

Lars-Åke