Write a formula using two arrays, across two worksheets

  • Thread starter Thread starter Susan
  • Start date Start date
S

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
 
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
Click to expand...


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
 
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,
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.
Click to expand...


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
 
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
 
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
Click to expand...


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

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

Lars-Åke
 
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
 
Yes, you can.
--
David Biddulph

Susan said:
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
Click to expand...
 
Well, seeing that you are starting with a SUMPRODUCT, why not just include
another term in that function?
 
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)...
 
Back
Top