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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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.....

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

1-10 inclusive...

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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 Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
<[email protected]> 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

On Sat, 25 Jun 2005 14:31:09 -0500, duane
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
<[email protected]> 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

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" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>