[SOLVED] sumif range to end of data

1. [SOLVED] sumif range to end of data

Thanks for any help.
I use a SUMIF function to sum values based upon a label in another range. Is
it possible to use a range that implies till the bottom of the data? There is
something like that in Visual Basic, I believe it is range.XlUp or
range.XlDown. I change the range not too often, so it is not a big deal, but
I would like to make it so it is automatic.
The formula I use now is:
SUMIF(A\$2:A\$991,A9,J\$2:J\$991)
which is inside:
I figure maybe there is something like:
SUMIF(A\$2:A\$END,A9,J\$2,J\$END)
or something like that?
Thanks for any help.

2. Re: sumif range to end of data

Why not just

SUMIF(A:A,A9,J:J)

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ian Elliott" <IanElliott@discussions.microsoft.com> wrote in message
news:DBE0DEA0-D5C1-4752-91F6-BB32714A57E8@microsoft.com...
> Thanks for any help.
> I use a SUMIF function to sum values based upon a label in another range.

Is
> it possible to use a range that implies till the bottom of the data? There

is
> something like that in Visual Basic, I believe it is range.XlUp or
> range.XlDown. I change the range not too often, so it is not a big deal,

but
> I would like to make it so it is automatic.
> The formula I use now is:
> SUMIF(A\$2:A\$991,A9,J\$2:J\$991)
> which is inside:
>

> I figure maybe there is something like:
> SUMIF(A\$2:A\$END,A9,J\$2,J\$END)
> or something like that?
> Thanks for any help.
>

3. Re: sumif range to end of data

Thanks Bob!

"Bob Phillips" wrote:

> Why not just
>
> SUMIF(A:A,A9,J:J)
>
>
> --
> HTH
>
> Bob Phillips
>
> (replace somewhere in email address with gmail if mailing direct)
>
> "Ian Elliott" <IanElliott@discussions.microsoft.com> wrote in message
> news:DBE0DEA0-D5C1-4752-91F6-BB32714A57E8@microsoft.com...
> > Thanks for any help.
> > I use a SUMIF function to sum values based upon a label in another range.

> Is
> > it possible to use a range that implies till the bottom of the data? There

> is
> > something like that in Visual Basic, I believe it is range.XlUp or
> > range.XlDown. I change the range not too often, so it is not a big deal,

> but
> > I would like to make it so it is automatic.
> > The formula I use now is:
> > SUMIF(A\$2:A\$991,A9,J\$2:J\$991)
> > which is inside:
> >

> > I figure maybe there is something like:
> > SUMIF(A\$2:A\$END,A9,J\$2,J\$END)
> > or something like that?
> > Thanks for any help.
> >

>
>
>

4. 2 column range - sumif

I have 2 columns... A & B that need to match with 2 other columns in another sheet (Sheet2.A & Sheet2.B) before I add the values in column C. I did SUMIF(A2:B1000;SHEET2.A2:B2;C2:1000) but it gives me a #VALUE error. How should I write my formula?

5. Re: sumif range to end of data

=INDEX(Sheet2!C2:C1000,MATCH(1,(Sheet2!A2:A1000=A2)*(Sheet2!B2:B1000=B2),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"maricelsy" <maricelsy.2ayuq7_1152932409.6601@excelforum-nospam.com> wrote
in message news:maricelsy.2ayuq7_1152932409.6601@excelforum-nospam.com...
>
> I have 2 columns... A & B that need to match with 2 other columns in
> another sheet (Sheet2.A & Sheet2.B) before I add the values in column
> C. I did SUMIF(A2:B1000;SHEET2.A2:B2;C2:1000) but it gives me a #VALUE
> error. How should I write my formula?
>
>
> --
> maricelsy
> ------------------------------------------------------------------------
> maricelsy's Profile:

http://www.excelforum.com/member.php...o&userid=36383
>

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1