Vlookup Count confusion !
 

The scenario is that i have 2 worksheets to link together.

In Worksheet 1 i have the following:
3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1)
5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40,
41-50


In Worksheet 2 i have the following data in 2 columns:
8 (in A1) 1 (in B1)
9 (in A2) 2 (in B2)
10 (in A3) 2 (in B3 etc)
21 3
7 1
44 1 etc
The question is, In sheet 1, can i get a formula to count the number
of (for example) 1's in the range of (for example) 1-10 by looking at
the data in sheet 2, and returning the number in Sheet 1 B2. In this
example, the number of 1's in the range 1-10 is 2.... But whats the
formula ??

I've been looking at Vlookup and Count functions and IF functions but
no joy yet, so if anyone can help please? Many thanks


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

Try something like this, Andy:
=SUMIF(Sheet2!$A$1:$A$6,"<="&A2,Sheet2!$B$1:$B$6)
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Andy787878" wrote
in message ...

The scenario is that i have 2 worksheets to link together.

In Worksheet 1 i have the following:
3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1)
5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40,
41-50


In Worksheet 2 i have the following data in 2 columns:
8 (in A1) 1 (in B1)
9 (in A2) 2 (in B2)
10 (in A3) 2 (in B3 etc)
21 3
7 1
44 1 etc
The question is, In sheet 1, can i get a formula to count the number
of (for example) 1's in the range of (for example) 1-10 by looking at
the data in sheet 2, and returning the number in Sheet 1 B2. In this
example, the number of 1's in the range 1-10 is 2.... But whats the
formula ??

I've been looking at Vlookup and Count functions and IF functions but
no joy yet, so if anyone can help please? Many thanks


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile:
http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

Oh! By the way, Andy, change your values from (for instance) 1-10 to just
10. If you need the "1 to" part, then put it in a previous column.
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Andy787878" wrote
in message ...

The scenario is that i have 2 worksheets to link together.

In Worksheet 1 i have the following:
3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1)
5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40,
41-50


In Worksheet 2 i have the following data in 2 columns:
8 (in A1) 1 (in B1)
9 (in A2) 2 (in B2)
10 (in A3) 2 (in B3 etc)
21 3
7 1
44 1 etc
The question is, In sheet 1, can i get a formula to count the number
of (for example) 1's in the range of (for example) 1-10 by looking at
the data in sheet 2, and returning the number in Sheet 1 B2. In this
example, the number of 1's in the range 1-10 is 2.... But whats the
formula ??

I've been looking at Vlookup and Count functions and IF functions but
no joy yet, so if anyone can help please? Many thanks


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile:
http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

Now you've got me confused. LOL. Try this, Andy:
http://www.mrexcel.com/td0128.html
*******************
~Anne Troy

www.OfficeArticles.com
www.MyExpertsOnline.com


"Andy787878" wrote
in message ...

The scenario is that i have 2 worksheets to link together.

In Worksheet 1 i have the following:
3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1)
5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40,
41-50


In Worksheet 2 i have the following data in 2 columns:
8 (in A1) 1 (in B1)
9 (in A2) 2 (in B2)
10 (in A3) 2 (in B3 etc)
21 3
7 1
44 1 etc
The question is, In sheet 1, can i get a formula to count the number
of (for example) 1's in the range of (for example) 1-10 by looking at
the data in sheet 2, and returning the number in Sheet 1 B2. In this
example, the number of 1's in the range 1-10 is 2.... But whats the
formula ??

I've been looking at Vlookup and Count functions and IF functions but
no joy yet, so if anyone can help please? Many thanks


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile:
http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

a little unclear but

=sumproduct((sheet2!a1:a100=1)*(sheet2!a1:a100<=1 0)*(sheet2!b1:b100=1))

will return a count of the # of 1's in column b sheet 2 where column a
is in the range of 1 to 10

If you are trying to refer to the column a entry in sheet 1 of "1 -
10", I think you will have difficulty, though you could neter 1 in say
column f and 10 in column g and refer to those cells in the sum product
(instead of "=1" use "=f2" if the 1-10 row is row 2. The column a
entry would then be =f2&" - "&g2

hope this helps


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=382197

Hello Anne and thanks for your response.

The answer to your 'answer' gives a return of 6 (1+2+2+1) for the range
1-10.

I'd like it to return an answer of the how many 1's there are in the
range 1-10 (so the right answer is '2' ....i'e there are 2 1's in the
range 1-10.

I think we're nearly there....!


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

since you have two critieria - only count "1"'s and only for column a in
range of 1-10 you need the sumproduct formula.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=382197

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
wrote:


The scenario is that i have 2 worksheets to link together.

In Worksheet 1 i have the following:
3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1)
5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40,
41-50


In Worksheet 2 i have the following data in 2 columns:
8 (in A1) 1 (in B1)
9 (in A2) 2 (in B2)
10 (in A3) 2 (in B3 etc)
21 3
7 1
44 1 etc
The question is, In sheet 1, can i get a formula to count the number
of (for example) 1's in the range of (for example) 1-10 by looking at
the data in sheet 2, and returning the number in Sheet 1 B2. In this
example, the number of 1's in the range 1-10 is 2.... But whats the
formula ??

What does "the number of 1's in the range 1-10" mean, exactly? What is the
significance of "1-10" in other words. Or why isn't the correct answer merely
the count of 1's (B1, B5, B6) = 3?


--ron

as far as i can tell it is because the 44 in column a corresponding to
the 3rd 1 in column b is not in the range of 1 to 10. But, the
question is not very clear.


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=382197

On Sat, 25 Jun 2005 14:31:09 -0500, duane
wrote:


as far as i can tell it is because the 44 in column a corresponding to
the 3rd 1 in column b is not in the range of 1 to 10. But, the
question is not very clear.

You're probably correct. Let's see if he clarifies things.


--ron

Sorry for confusing you...it's because i'm confused !

To clarify,

I'd like the formula to count the number of 1's in column 2, if the
number on the same row in column 1 is between 1 and 10.

So the scenario is, in column 1, I have the following numbers
8 in A1
9 in A2
10 in A3
21 in A4
7 in A5
44 in A6

In column 2 I have
1 in B1
2 in B2
2 in B3
3 in B4
1 in B5
1 in B6

The formula i'd like in (say) C1 is to Count the number of 1's that
occur in column 2, only if the number in the same row in Column 1 is in
the range 1-10.

So the answer i'd like would be 2 in this example. Because a 1 in
Column 2 is only adjacent twice to a number between 1-10 in column 1
(the numbers being 8 (A1) and 7 (A5))

Sorry Duane, Ron, Anne.....


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

SO ... Duane's Sumproduct formula works *exactly* as you specified.

Have you tried it?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Andy787878" wrote
in message ...

Sorry for confusing you...it's because i'm confused !

To clarify,

I'd like the formula to count the number of 1's in column 2, if the
number on the same row in column 1 is between 1 and 10.

So the scenario is, in column 1, I have the following numbers
8 in A1
9 in A2
10 in A3
21 in A4
7 in A5
44 in A6

In column 2 I have
1 in B1
2 in B2
2 in B3
3 in B4
1 in B5
1 in B6

The formula i'd like in (say) C1 is to Count the number of 1's that
occur in column 2, only if the number in the same row in Column 1 is in
the range 1-10.

So the answer i'd like would be 2 in this example. Because a 1 in
Column 2 is only adjacent twice to a number between 1-10 in column 1
(the numbers being 8 (A1) and 7 (A5))

Sorry Duane, Ron, Anne.....


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile:
http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
wrote:


Sorry for confusing you...it's because i'm confused !

To clarify,

I'd like the formula to count the number of 1's in column 2, if the
number on the same row in column 1 is between 1 and 10.

So the scenario is, in column 1, I have the following numbers
8 in A1
9 in A2
10 in A3
21 in A4
7 in A5
44 in A6

In column 2 I have
1 in B1
2 in B2
2 in B3
3 in B4
1 in B5
1 in B6

The formula i'd like in (say) C1 is to Count the number of 1's that
occur in column 2, only if the number in the same row in Column 1 is in
the range 1-10.

So the answer i'd like would be 2 in this example. Because a 1 in
Column 2 is only adjacent twice to a number between 1-10 in column 1
(the numbers being 8 (A1) and 7 (A5))

Sorry Duane, Ron, Anne.....

And what happens when you use Duane's formula? It appears as if it should work
according to these specs.


--ron

****Duane - You Are A Star****

and many, many thanks to everyone who replied...


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197

the only thing i would add is are you looking for 1-10 inclusive, or
"between 1 and 10" ie 2-9 inclusive?


--
duane


------------------------------------------------------------------------
duane's Profile: http://www.excelforum.com/member.php...o&userid=11624
View this thread: http://www.excelforum.com/showthread...hreadid=382197

1-10 inclusive...


--
Andy787878
------------------------------------------------------------------------
Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792
View this thread: http://www.excelforum.com/showthread...hreadid=382197