Vlookup Count confusion !

  • Thread starter Thread starter Andy787878
  • Start date Start date
A

Andy787878

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
 
a little unclear but

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

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

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

hope this helps
 
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....!
 
since you have two critieria - only count "1"'s and only for column a i
range of 1-10 you need the sumproduct formula
 
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 ??
Click to expand...

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


--ron
 
as far as i can tell it is because the 44 in column a corresponding t
the 3rd 1 in column b is not in the range of 1 to 10. But, th
question is not very clear
 
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.
Click to expand...

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

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


--ron
 
the only thing i would add is are you looking for 1-10 inclusive, o
"between 1 and 10" ie 2-9 inclusive
 
Back
Top