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