[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:
=J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
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.

Why not just

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


--
HTH

Bob Phillips

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

"Ian Elliott" <[email protected]> wrote in message
news:[email protected]...
> 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:
>
=J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,
IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
> 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.
>

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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:
> >
> =J9/SUMIF(A$2:A$991,A9,J$2:J$991)*IF(A9="BEUL",Adj!X$3,IF(A9="FEUL",Adj!X$4,
> IF(A9="AUL",Adj!X$5,"ERROR!")))/N9
> > 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.
> >
>
>
>

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?
I appreciate your help.

=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" <[email protected]> wrote
in message news:[email protected]...
>
> 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?
> I appreciate your help.
>
>
> --
> maricelsy
> ------------------------------------------------------------------------
> maricelsy's Profile:
http://www.excelforum.com/member.php...o&userid=36383
> View this thread: http://www.excelforum.com/showthread...hreadid=561543
>