Write a formula using two arrays, across two worksheets

[Susan]

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

 

[Lars-Åke Aspelin]

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

 

[Vicki]

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]

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.

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

 

[Susan]

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]

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

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

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

Lars-Åke

 

[Susan]

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

 

[David Biddulph]

Yes, you can.
--
David Biddulph

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

 

[Susan]

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

 

[David Biddulph]

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

 

[Susan]

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

 

[David Biddulph]

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