Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup Count confusion !
The scenario is that i have 2 worksheets to link together. In Worksheet 1 i have the following: 3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1) 5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40, 41-50 In Worksheet 2 i have the following data in 2 columns: 8 (in A1) 1 (in B1) 9 (in A2) 2 (in B2) 10 (in A3) 2 (in B3 etc) 21 3 7 1 44 1 etc The question is, In sheet 1, can i get a formula to count the number of (for example) 1's in the range of (for example) 1-10 by looking at the data in sheet 2, and returning the number in Sheet 1 B2. In this example, the number of 1's in the range 1-10 is 2.... But whats the formula ?? I've been looking at Vlookup and Count functions and IF functions but no joy yet, so if anyone can help please? Many thanks -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#2
|
|||
|
|||
Try something like this, Andy:
=SUMIF(Sheet2!$A$1:$A$6,"<="&A2,Sheet2!$B$1:$B$6) ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Andy787878" wrote in message ... The scenario is that i have 2 worksheets to link together. In Worksheet 1 i have the following: 3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1) 5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40, 41-50 In Worksheet 2 i have the following data in 2 columns: 8 (in A1) 1 (in B1) 9 (in A2) 2 (in B2) 10 (in A3) 2 (in B3 etc) 21 3 7 1 44 1 etc The question is, In sheet 1, can i get a formula to count the number of (for example) 1's in the range of (for example) 1-10 by looking at the data in sheet 2, and returning the number in Sheet 1 B2. In this example, the number of 1's in the range 1-10 is 2.... But whats the formula ?? I've been looking at Vlookup and Count functions and IF functions but no joy yet, so if anyone can help please? Many thanks -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#3
|
|||
|
|||
Oh! By the way, Andy, change your values from (for instance) 1-10 to just
10. If you need the "1 to" part, then put it in a previous column. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Andy787878" wrote in message ... The scenario is that i have 2 worksheets to link together. In Worksheet 1 i have the following: 3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1) 5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40, 41-50 In Worksheet 2 i have the following data in 2 columns: 8 (in A1) 1 (in B1) 9 (in A2) 2 (in B2) 10 (in A3) 2 (in B3 etc) 21 3 7 1 44 1 etc The question is, In sheet 1, can i get a formula to count the number of (for example) 1's in the range of (for example) 1-10 by looking at the data in sheet 2, and returning the number in Sheet 1 B2. In this example, the number of 1's in the range 1-10 is 2.... But whats the formula ?? I've been looking at Vlookup and Count functions and IF functions but no joy yet, so if anyone can help please? Many thanks -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#4
|
|||
|
|||
Now you've got me confused. LOL. Try this, Andy:
http://www.mrexcel.com/td0128.html ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Andy787878" wrote in message ... The scenario is that i have 2 worksheets to link together. In Worksheet 1 i have the following: 3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1) 5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40, 41-50 In Worksheet 2 i have the following data in 2 columns: 8 (in A1) 1 (in B1) 9 (in A2) 2 (in B2) 10 (in A3) 2 (in B3 etc) 21 3 7 1 44 1 etc The question is, In sheet 1, can i get a formula to count the number of (for example) 1's in the range of (for example) 1-10 by looking at the data in sheet 2, and returning the number in Sheet 1 B2. In this example, the number of 1's in the range 1-10 is 2.... But whats the formula ?? I've been looking at Vlookup and Count functions and IF functions but no joy yet, so if anyone can help please? Many thanks -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#5
|
|||
|
|||
a little unclear but =sumproduct((sheet2!a1:a100=1)*(sheet2!a1:a100<=1 0)*(sheet2!b1:b100=1)) will return a count of the # of 1's in column b sheet 2 where column a is in the range of 1 to 10 If you are trying to refer to the column a entry in sheet 1 of "1 - 10", I think you will have difficulty, though you could neter 1 in say column f and 10 in column g and refer to those cells in the sum product (instead of "=1" use "=f2" if the 1-10 row is row 2. The column a entry would then be =f2&" - "&g2 hope this helps -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#6
|
|||
|
|||
Hello Anne and thanks for your response. The answer to your 'answer' gives a return of 6 (1+2+2+1) for the range 1-10. I'd like it to return an answer of the how many 1's there are in the range 1-10 (so the right answer is '2' ....i'e there are 2 1's in the range 1-10. I think we're nearly there....! -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#7
|
|||
|
|||
since you have two critieria - only count "1"'s and only for column a in range of 1-10 you need the sumproduct formula. -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#8
|
|||
|
|||
On Sat, 25 Jun 2005 08:36:51 -0500, Andy787878
wrote: The scenario is that i have 2 worksheets to link together. In Worksheet 1 i have the following: 3 Columns, marked 1 (in B1), 2 (in C1), 3 (in D1) 5 rows marked 1-10 (in A2), 11-20 (in A3), 21-30 (in A4 etc), 31-40, 41-50 In Worksheet 2 i have the following data in 2 columns: 8 (in A1) 1 (in B1) 9 (in A2) 2 (in B2) 10 (in A3) 2 (in B3 etc) 21 3 7 1 44 1 etc The question is, In sheet 1, can i get a formula to count the number of (for example) 1's in the range of (for example) 1-10 by looking at the data in sheet 2, and returning the number in Sheet 1 B2. In this example, the number of 1's in the range 1-10 is 2.... But whats the formula ?? What does "the number of 1's in the range 1-10" mean, exactly? What is the significance of "1-10" in other words. Or why isn't the correct answer merely the count of 1's (B1, B5, B6) = 3? --ron |
#9
|
|||
|
|||
as far as i can tell it is because the 44 in column a corresponding to the 3rd 1 in column b is not in the range of 1 to 10. But, the question is not very clear. -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#10
|
|||
|
|||
On Sat, 25 Jun 2005 14:31:09 -0500, duane
wrote: as far as i can tell it is because the 44 in column a corresponding to the 3rd 1 in column b is not in the range of 1 to 10. But, the question is not very clear. You're probably correct. Let's see if he clarifies things. --ron |
#11
|
|||
|
|||
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 |
#12
|
|||
|
|||
SO ... Duane's Sumproduct formula works *exactly* as you specified.
Have you tried it? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Andy787878" wrote in message ... Sorry for confusing you...it's because i'm confused ! To clarify, I'd like the formula to count the number of 1's in column 2, if the number on the same row in column 1 is between 1 and 10. So the scenario is, in column 1, I have the following numbers 8 in A1 9 in A2 10 in A3 21 in A4 7 in A5 44 in A6 In column 2 I have 1 in B1 2 in B2 2 in B3 3 in B4 1 in B5 1 in B6 The formula i'd like in (say) C1 is to Count the number of 1's that occur in column 2, only if the number in the same row in Column 1 is in the range 1-10. So the answer i'd like would be 2 in this example. Because a 1 in Column 2 is only adjacent twice to a number between 1-10 in column 1 (the numbers being 8 (A1) and 7 (A5)) Sorry Duane, Ron, Anne..... -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#13
|
|||
|
|||
On Sun, 26 Jun 2005 05:02:11 -0500, Andy787878
wrote: Sorry for confusing you...it's because i'm confused ! To clarify, I'd like the formula to count the number of 1's in column 2, if the number on the same row in column 1 is between 1 and 10. So the scenario is, in column 1, I have the following numbers 8 in A1 9 in A2 10 in A3 21 in A4 7 in A5 44 in A6 In column 2 I have 1 in B1 2 in B2 2 in B3 3 in B4 1 in B5 1 in B6 The formula i'd like in (say) C1 is to Count the number of 1's that occur in column 2, only if the number in the same row in Column 1 is in the range 1-10. So the answer i'd like would be 2 in this example. Because a 1 in Column 2 is only adjacent twice to a number between 1-10 in column 1 (the numbers being 8 (A1) and 7 (A5)) Sorry Duane, Ron, Anne..... And what happens when you use Duane's formula? It appears as if it should work according to these specs. --ron |
#14
|
|||
|
|||
****Duane - You Are A Star**** and many, many thanks to everyone who replied... -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#15
|
|||
|
|||
the only thing i would add is are you looking for 1-10 inclusive, or "between 1 and 10" ie 2-9 inclusive? -- duane ------------------------------------------------------------------------ duane's Profile: http://www.excelforum.com/member.php...o&userid=11624 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
#16
|
|||
|
|||
1-10 inclusive... -- Andy787878 ------------------------------------------------------------------------ Andy787878's Profile: http://www.excelforum.com/member.php...o&userid=15792 View this thread: http://www.excelforum.com/showthread...hreadid=382197 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
Conversion | Excel Worksheet Functions | |||
SUMPRODUCT Formula to Count Row of data Below Matched Criteria | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |