|
Collect numbers....
Hi!
First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- |
It almost looks like you could use =sumif()
=sumif(a1:A999,"Ivan",E1:e999) But it kind of scares me why you were using TRUE in your =vlookup()'s. I would have guessed that you would have wanted False. Zadig Galbaras wrote: Hi! First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- -- Dave Peterson |
Of course your right, FALSE it is.
In Norwegian there is only one letter differing FALSE from TRUE, namely USANN and SANN. But this SUMIF() didn't return the right number. The correct number would be 146, SUMIF() returned 163 and that's a 17 point miss. In fact the number was off in all six calculations. I have now used VLOOKUP() and divided the calculation into smaller parts which ends up in a neat table which I can sort. A detour, but it works. I wonder why the SUMIF() returned the wrong result? -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Dave Peterson" skrev i melding ... It almost looks like you could use =sumif() =sumif(a1:A999,"Ivan",E1:e999) But it kind of scares me why you were using TRUE in your =vlookup()'s. I would have guessed that you would have wanted False. Zadig Galbaras wrote: Hi! First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- -- Dave Peterson |
My guess is that at least one of those numbers being returned isn't a number.
I'm guessing it's text. You can convert those "text numbers" to "number numbers" by: Selecting an empty cell edit|copy select the range to fix Edit|Paste special|Check Add ===== The reason the =vlookup()'s worked is that excel likes to help. If you have a formula like: ="1"+2 You'll get 3. Excel will see that you're trying to do arithmetic with what looks like a number, so it'll treat it like a number. =sumif() isn't as forgiving. == And you may want to start writing your =vlookup() using 0. =vlookup(a1,sheet2!a:e,5,0) (0 and false would be equivalent.) Zadig Galbaras wrote: Of course your right, FALSE it is. In Norwegian there is only one letter differing FALSE from TRUE, namely USANN and SANN. But this SUMIF() didn't return the right number. The correct number would be 146, SUMIF() returned 163 and that's a 17 point miss. In fact the number was off in all six calculations. I have now used VLOOKUP() and divided the calculation into smaller parts which ends up in a neat table which I can sort. A detour, but it works. I wonder why the SUMIF() returned the wrong result? -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Dave Peterson" skrev i melding ... It almost looks like you could use =sumif() =sumif(a1:A999,"Ivan",E1:e999) But it kind of scares me why you were using TRUE in your =vlookup()'s. I would have guessed that you would have wanted False. Zadig Galbaras wrote: Hi! First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- -- Dave Peterson -- Dave Peterson |
Hi, Dave:
But the result returned by SUMIF was HIGHER, not lower... There must be another factor at work here. Myrna Larson On Sat, 24 Sep 2005 13:58:43 -0500, Dave Peterson wrote: My guess is that at least one of those numbers being returned isn't a number. I'm guessing it's text. You can convert those "text numbers" to "number numbers" by: Selecting an empty cell edit|copy select the range to fix Edit|Paste special|Check Add ===== The reason the =vlookup()'s worked is that excel likes to help. If you have a formula like: ="1"+2 You'll get 3. Excel will see that you're trying to do arithmetic with what looks like a number, so it'll treat it like a number. =sumif() isn't as forgiving. == And you may want to start writing your =vlookup() using 0. =vlookup(a1,sheet2!a:e,5,0) (0 and false would be equivalent.) Zadig Galbaras wrote: Of course your right, FALSE it is. In Norwegian there is only one letter differing FALSE from TRUE, namely USANN and SANN. But this SUMIF() didn't return the right number. The correct number would be 146, SUMIF() returned 163 and that's a 17 point miss. In fact the number was off in all six calculations. I have now used VLOOKUP() and divided the calculation into smaller parts which ends up in a neat table which I can sort. A detour, but it works. I wonder why the SUMIF() returned the wrong result? -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Dave Peterson" skrev i melding ... It almost looks like you could use =sumif() =sumif(a1:A999,"Ivan",E1:e999) But it kind of scares me why you were using TRUE in your =vlookup()'s. I would have guessed that you would have wanted False. Zadig Galbaras wrote: Hi! First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- -- Dave Peterson |
Doh. You read those responses too close <vbg!
To the original poster: Any chance that there another row not part of your tables (maybe between the tables) that had "ivan" in it? Or you forgot to add an =vlookup() for all the tables? If you select your range and apply Data|Filter|autofilter, you could filter to just show Ivan in that column. What's the sum of those visible cells in that other column? or.... Any chance that you transposed the numbers in the post--sumif() was smaller and =vlookup()'s was larger? Myrna Larson wrote: Hi, Dave: But the result returned by SUMIF was HIGHER, not lower... There must be another factor at work here. Myrna Larson On Sat, 24 Sep 2005 13:58:43 -0500, Dave Peterson wrote: My guess is that at least one of those numbers being returned isn't a number. I'm guessing it's text. You can convert those "text numbers" to "number numbers" by: Selecting an empty cell edit|copy select the range to fix Edit|Paste special|Check Add ===== The reason the =vlookup()'s worked is that excel likes to help. If you have a formula like: ="1"+2 You'll get 3. Excel will see that you're trying to do arithmetic with what looks like a number, so it'll treat it like a number. =sumif() isn't as forgiving. == And you may want to start writing your =vlookup() using 0. =vlookup(a1,sheet2!a:e,5,0) (0 and false would be equivalent.) Zadig Galbaras wrote: Of course your right, FALSE it is. In Norwegian there is only one letter differing FALSE from TRUE, namely USANN and SANN. But this SUMIF() didn't return the right number. The correct number would be 146, SUMIF() returned 163 and that's a 17 point miss. In fact the number was off in all six calculations. I have now used VLOOKUP() and divided the calculation into smaller parts which ends up in a neat table which I can sort. A detour, but it works. I wonder why the SUMIF() returned the wrong result? -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- "Dave Peterson" skrev i melding ... It almost looks like you could use =sumif() =sumif(a1:A999,"Ivan",E1:e999) But it kind of scares me why you were using TRUE in your =vlookup()'s. I would have guessed that you would have wanted False. Zadig Galbaras wrote: Hi! First of all let me thank you all for all your help. So.... I have this spreadsheet containing twelve tables where six different persons points are represented. There are tables showing a ranked list over whos got the most points in twelve different areas. Is there a way to collect all points from all twelve tables into an overall table? All twelve tables are placed in a secluded area and can be marked in one area. The VLOOKUP do work if I add twelve of them together like this VLOOKUP("IVAN";A1:E6;5;TRUE)+VLOOKUP("IVAN";A10:E1 6;5;TRUE)+ and so on........, but......there must be an easier way? Each persons points are always four columns to the left of their name, i.e. the name on the first and his points at the fifth in a matrix. The name on a row will vary as this is ranked lists. Is there a function I can use. -- Regards Zadig Galbaras A Perturbed Norwegian Agnostic ----- -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 07:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com