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:E16;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:E16;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" <petersod@verizonXSPAM.net> skrev i melding
news:433592A6.36FAA35E@verizonXSPAM.net...
> 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:E16;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" <petersod@verizonXSPAM.net> skrev i melding
> news:433592A6.36FAA35E@verizonXSPAM.net...
> > 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:E16;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 <petersod@verizonXSPAM.net>
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" <petersod@verizonXSPAM.net> skrev i melding
>> news:433592A6.36FAA35E@verizonXSPAM.net...
>> > 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:E16;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 <petersod@verizonXSPAM.net>
> 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" <petersod@verizonXSPAM.net> skrev i melding
> >> news:433592A6.36FAA35E@verizonXSPAM.net...
> >> > 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:E16;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